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:

$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:

$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:


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:


WHERE (time LIKE '%$date%')

[Jakeman]

Originally posted by futureal

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:


$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:

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 (https://vborg.vbsupport.ru/showthread.php?s=&threadid=47022) 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. :)

[Jakeman]

The new WHERE clause produces this error, oddly enough :confused:

Parse error: parse error in /server path here... on line 223

Fatal error: Call to undefined function: msc_portal_events() in /server path here... on line 10

msc_portal_events is the name of the function that contains this query. When I remove the WHERE clause or use a different clause, the function is defined again. Something is fubar with your last WHERE clause.

I appreciate your help with this problem. :)

Re: your thread
I just checked the mod forum and there is already a thread about your problem posted in there. Hopefully Firefly will get to it soon.

[futureal]

That's weird. When I run it through a MySQL command line it works OK. Let me play around with it a bit and I'll see if I can come up with a fix...

[futureal]

I can run this query on my vBulletin table:


SELECT * FROM calendar_events WHERE (UNIX_TIMESTAMP(CONCAT(CURRENT_DATE, " 00:00:00"))+86400) >= UNIX_TIMESTAMP(eventdate);

...and it works.

I'm not sure what would be producing an error, since those are all valid MySQL expressions.

[futureal]

Oh.

I just figured it out, I think. I failed to comprehend that the error you were getting was a parse error. :)

Make sure to escape the quotes inside the query. So in vBulletin code, for example, it would look something like:

$DB_site->query("SELECT * FROM calendar_events WHERE (UNIX_TIMESTAMP(CONCAT(CURRENT_DATE, ' 00:00:00'))+86400) >= UNIX_TIMESTAMP(eventdate)");

Now that should work.

[Jakeman]

It's working now. And it also considers the time offset of the logged in user. :)

Here is the final code:


$msc_date = vbdate("Y-m-d",$ourtimenow);
$msc_date = $msc_date . " 00:00:00";

...

WHERE (UNIX_TIMESTAMP('$msc_date')) <= UNIX_TIMESTAMP(calendar_events.eventdate)

...

[futureal]

Cool, glad it works!

[Jakeman]

The final product... http://www.mscclan.com/ (events in the left column)

It was part of my event signup system I have been making.

[futureal]

Cool, looks good.

Makes me want to bust out Diablo II again. Haven't played it for like a year! haha

[Jakeman]

I have a level 99 tank, mf, dueling sorceress. :D