Hi.
In my MySQL database, there is a table, where I store daily recurring events.
I store the time of the event in the field time, that has the 'TIME' type. How do I select all events within the next hour?
views:
23answers:
2
+1
A:
Something like this:
TIMEDIFF(event.time, TIME(NOW())) < TIME('01:00:00')
Sjoerd
2010-06-30 09:59:21
+2
A:
Try this:
SELECT *
FROM your_table
WHERE TIMEDIFF(`time`,TIME(NOW())) < TIME('01:00:00');
jigfox
2010-06-30 10:00:53
Thank you for your answer. This query seems not to work properly. for the current time (which is 12:36 for me right now), it returns values from 01:10:00 to 13:37:00. (see http://www.fishnation.de/bachelor/code/caching/wvv/wvv-select.php ). Is it because of the 24h-format I'm using?
snorpey
2010-06-30 10:41:33
it should work in 24h format. try to encapsulate your `time` field with backticks(```), like i did in my query. I'm not really sure, but I think `time` is a keyword in mysql, therefor it must be marked as column by surrounding it with the backticks
jigfox
2010-06-30 10:48:58
as Sjoerd wrote, it has to be `< TIME('01:00:00')` to work... thanks for your efforts, though!
snorpey
2010-06-30 11:00:35