selecting calendar events

[Jakeman]

I am trying to select calendar events whose eventdates are equal to or greater than the current day (events that haven't already passed). I am having trouble with the WHERE part of the query.

The event dates are stored in YYYY-MM-DD format in the database and I can't figure a way to compare a date in that format in the WHERE line... nor have I been successful in converting the selected eventdate to a UNIX_TIMESTAMP for comparison in the WHERE line (is that even possible?).

Help. Thanks.

[filburt1]

Are these vB timestamps or your own table (you mentioned YYYY-MM-DD and vB doesn't store them like that IIRC)?

[Jakeman]

Originally posted by filburt1
Are these vB timestamps or your own table (you mentioned YYYY-MM-DD and vB doesn't store them like that IIRC)?

They are vB's. I think calendar_events.eventdate is the only date field in vB that uses that format instead of the unix timestamp. It's annoying.

I still haven't been able to figure this out.

[futureal]

You should be able to compare it to a string containing today's date. Use something like this to get today's date:

PHP Code:

$today = date("Y-m-d",time()); 


And from there, you can just do a normal WHERE clause, such as "WHERE calendardate > $today" or whatever you need.

[Jakeman]

Originally posted by futureal
You should be able to compare it to a string containing today's date. Use something like this to get today's date:

PHP Code:

$today = date("Y-m-d",time()); 


And from there, you can just do a normal WHERE clause, such as "WHERE calendardate > $today" or whatever you need.

I tried that. You can't compare strings like that.

2002-12-15 >= 2002-12-18 doesn't work.

I think I need to compare them in timestamp format but I can't figure out a way to do that.

This doesn't work:
SELECT eventdate FROM calendar_events WHERE eventdate >= $today

I think I need to somehow convert the selected eventdate to timestamp format, but I haven't been able to figure out how to convert the selection in a WHERE clause... I'm beginning to think it's not possible.

[Jakeman]

I was thinking... is there a wildcard for comparing strings in a WHERE clause?

For example, something like:

WHERE eventdate = 2002-12-**

In this example, the query would return all events in December. Is there any tool like this?

[futureal]

Originally posted by Jakeman

I tried that. You can't compare strings like that.

2002-12-15 >= 2002-12-18 doesn't work.

This should work in MySQL... or at least, it has worked in scripts I have written in the past. If a MySQL field is of the DATE type, the software knows how to compare one date to another.

However, there is another, slightly cleaner option, now that I think about it. Try this:

PHP Code:

SELECT whatever FROM table WHERE UNIX_TIMESTAMP() >= UNIX_TIMESTAMP(date_field); 


Give that a shot and let me know if it works.

Note that the UNIX_TIMESTAMP() function returns the current timestamp when called with no argument.

[Sebastian]

how about something like:

$today = date("Y-m-d",time());
$month = $today['mon'];
$day = $today['mday'];
$year = $today['year'];
$date = "$year-$month-$day";

fetching like:

[sql]
WHERE (time LIKE '%$date%')
[/sql]

[Jakeman]

Originally posted by futureal

PHP Code:

SELECT whatever FROM table WHERE UNIX_TIMESTAMP() >= UNIX_TIMESTAMP(date_field); 


That appears to be working correctly. Except that it isn't returning events that are equal to the current date, only events that are greater than...

I thought it might be a time offset issue so I tried using:

PHP Code:

$date = vbdate("Y-m-d",$ourtimenow);

...

WHERE UNIX_TIMESTAMP($date) >= ... 


That didn't work though... it returned all events, like the WHERE clause was always true.

Any ideas?

[futureal]

Originally posted by Jakeman
That appears to be working correctly. Except that it isn't returning events that are equal to the current date, only events that are greater than...

I thought it might be a time offset issue so I tried using:

Yea, in hindsight I can see where this check would fail.

Suppose is it 12:02 AM on January 1st. An event taking place at 12:10 AM on January 1st would still have a timestamp greater than the current time, while an event taking place at 12:01 AM would not (and would show up).

So, here's our solution:

PHP Code:

SELECT whatever FROM table WHERE (UNIX_TIMESTAMP(CONCAT(CURRENT_DATE, " 00:00:00"))+86400) >= UNIX_TIMESTAMP(date_field); 


Basically, we're taking today's date (CURRENT_DATE) at midnight and advancing it one full day (86400 seconds) so that we have the timestamp for tomorrow at midnight. Then, any dates that occur up to tomorrow at midnight should be displayed.

Give that a shot.

Oh, and if that does work, you can reward me by taking a look at this thread and see if you can track down one of the moderating corps who might be able to fix my hack thread for me so I can post in it again.