CODEWITHSUNDEEP
mysqldatetimetimestamp
Drew
Drew

Reputation: 6274

getting number of hours until the next event

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!

Upvotes: 0

Views: 276

Answers (1)

Déjà vu
Déjà vu

Reputation: 28850

The result can be obtained directly, using the timediff() MySQL function:

  SELECT timediff(lastFinish + INTERVAL 48 HOUR, now()) FROM cron_status;

should display the time as hh:mm:ss. Assuming lastFinish is a datetime.

In order to get the answer in hours instead,

  SELECT timestampdiff(HOUR, now(), lastFinish + INTERVAL 48 HOUR) FROM cron_status;

Note that timediff does arg1 - arg2 while timestampdiff does arg2 - arg1.

Upvotes: 3

Related Questions