getting number of hours until the next event
- by Andrew Heath
I've got a table with this data:
[ID] [event_name] [last_event]
1 stats 2011-01-01 01:47:32
last_event is a timestamp. The event occurs every 48 hours (it's a cron job). I'd like to show my users the number of hours until the event executes again.
So far I've got:
SELECT (lastFinish + INTERVAL 48 HOUR) FROM `cron_status`
which gives me the exact time and date of the next occurence: 2011-01-03 01:47:32. So I figured if I subtracted the current datetime...
SELECT ((lastFinish + INTERVAL 48 HOUR) - SYSDATE()) FROM `cron_status`
which (I think?) gives me the difference in unix time: 1980015. But if I divide that by 3600 to convert the seconds to hours...
SELECT (((lastFinish + INTERVAL 48 HOUR) - SYSDATE())/3600) FROM `cron_status`
I get numbers an order of magnitude too high: 549.99.
Where am I going wrong? The target is returning the number of hours until the next execution.
Thank you!
