Difference between two times in hours with MySQL and PHP


I have a MySQL table containing a list of times that certain jobs are due done by. I’m trying to find out how many hours there are between the due date and the current date.

ie. duedate = 2006-11-08 09:55:00

If the currentdate was 2006-11-08 11:05:00, I’d like to have 1.167 hrs as the return value.

Is there an easy way to do this? I’ve been trying to use DATEDIFF but with no success as of yet.

So, I saw this and i was like “Something that I’ve done before! A long time ago… except I forgot…” So I go digging around on the mysql site, as well as looking through my books… ah and low and behold…

SELECT TIMEDIFF(NOW(), time) as time FROM test;
| time     |
| 00:10:05 |
| 00:09:45 |
2 rows in set (0.03 sec)

mysql> SELECT TIMEDIFF(NOW(), time) as time FROM test;

lol… TIMEDIFF() thats great…

See? 10 minutes it took to script it! I am so great!


select ( unix_timestamp(current_timestamp)
       - unix_timestamp(duedate) ) / 3600.0 as hours
  from ...

Mine is prettier.

mine works before version 4.1

also, mine results in a decimal number as requested, e.g. 1.167

Thanks for the suggestions, I’ll give them both a shot…

r937 - is the unix_timestamp function a purely MySQL function? the reason I ask is that currently I’m testing on a unix based system, but may be moving to a windows based server shortly, and just want to confirm it would continue to work.

yes, it is

it will continue to work

works great thanks r937

r937 makes me sad.