How to get time difference (in hours) between 2 dates

Question

I am trying to get the time difference between 2 users, I need the difference in hours.

I tried to use DATEDIFF function but it's wrong.

Here is my code:

SELECT DATEDIFF(*,  
(SELECT max(u1.time_c)
FROM users u)
,
(SELECT max(u2.time_c)
FROM users u2)

What do you mean "*the time difference between 2 users*"? Are you referring to their timezones? Or something else? You say your existing code is "*wrong*" but don't say what's wrong with it. Are we supposed to guess? (My guess would be that [`DATEDIFF()`](http://dev.mysql.com/doc/en/date-and-time-functions.html#function_datediff) function returns a difference in *days*, not *hours*; and only takes two arguments, not three--but is that *all* that's wrong?). — eggyal, Jun 07 '12 at 06:35
if u.1 was created 2 days ago and u.2 was created yesterday then the difference is 24 hours for example — Ofer, Jun 07 '12 at 06:39

score 16 · Answer 1 · answered Jun 07 '12 at 06:37

Only the date parts of the values are used in the calculation.

You will want to look at TIMEDIFF

This will give you the number of hours in the difference in times (assuming your time_c fields are DATETIME or something similar)

SELECT HOUR(TIMEDIFF(  
  (SELECT max(u1.time_c) FROM users u),
  (SELECT max(u2.time_c) FROM users u2)
))

score 3 · Accepted Answer · answered Jun 07 '12 at 06:39

3

You must have a from clause in your select statement. Something like

Select date1 - date2 from dual

returns number of days between date1 and date2.

If you want number of hours:

Select (date1 - date2)*24 from dual;

(this is only for oracle)

answered Jun 07 '12 at 06:39

Florin Ghita

I don't understand your question, but if you asking about the type of result, the result is a floating point number, wich represents the number of days in the first query, and the number of hours in the second. For example, 0.5 days means 12 hours. another example: 0.2 hours = 0.2*60 minutes = 12 minutes – Florin Ghita Jun 07 '12 at 06:51
If date difference is zero then what happen? how can get hour? – Sadikhasan Nov 30 '13 at 10:51
in this case nothing happens. There will be zero hours. – Florin Ghita Dec 02 '13 at 09:31

2 Answers2