# Thread: How to calculate the difference between 2 dates

1. Registered User
Join Date
Jan 2004
Location
Australia
Posts
42

## Unanswered: How to calculate the difference between 2 dates

I have 2 columns are datetime field in a table.
I want to find the differnce of these 2 fields and convert into hours, minutes and seconds.

For example:

2004-05-10 17:10:23
and
2004-05-05 12:00:00

The difference should be 125:10:23

I need to do that in a SQL:
SELECT .... <-- calculation should be here
FROM....

2. Registered User
Join Date
Feb 2003
Location
Denver, CO
Posts
34
Check out DATE_SUB or DATEDIFF on the MySQL website ... one of these should do the trick:

http://dev.mysql.com/doc/mysql/en/Da...functions.html

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
DATE_SUB is not really useful in this situation

DATEDIFF is not available until 4.1.1, and it deals only in days

the following works fine, and also allows for dates more than 1 day apart
Code:
```concat(''
, case when
floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
)  > 0
then concat(
floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
) , ' day'
, case when
floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
)  = 1 then ', ' else 's, ' end
)
else '' end
, sec_to_time(
unix_timestamp(date2)
-unix_timestamp(date1)
- floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
) * 86400
)
,'.'
)              as result
from somedates2```

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
insert into somedates2 ( date1,date2 ) values
( '2004-05-05 12:00:00', '2004-05-10 17:10:23' )

test result: 5 days, 05:10:23.

note the hours/minutes/seconds is the output of the SEC_TO_TIME function, and, as such, it's a string

be aware that SEC_TO_TIME will handle conversions only less than 24 hrs

if you want to "add" the 5 days back, to make it display as 125:10:23., then you can do that using the SUBSTRING function

personally, i like to see the days split out

5. Registered User
Join Date
Jan 2004
Location
Australia
Posts
42
Hi,

Thank you for your code, it works for me !

However, I get another problem as I have a column is date type and a cloumn is time, how do I combine as a datetime so that I can put it into the date1 in your code ?

-------------------------------------------------------------------------

Originally Posted by r937
DATE_SUB is not really useful in this situation

DATEDIFF is not available until 4.1.1, and it deals only in days

the following works fine, and also allows for dates more than 1 day apart
Code:
```concat(''
, case when
floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
)  > 0
then concat(
floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
) , ' day'
, case when
floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
)  = 1 then ', ' else 's, ' end
)
else '' end
, sec_to_time(
unix_timestamp(date2)
-unix_timestamp(date1)
- floor(
( unix_timestamp(date2)
-unix_timestamp(date1) ) / 86400
) * 86400
)
,'.'
)              as result
from somedates2```

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
excuse me, but you said they were datetime columns

if you have separate date and time columns, why?

7. Registered User
Join Date
Jan 2004
Location
Australia
Posts
42
This table stores the date and time required for the Job.
There is another table called JobLog has one field to store the date time of the status of the job.

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
you should run a few tests to see how to combine the date and time values into a datetime value

i'm not gonna test it for you, sorry, i spent almost an hour working for you for free to come up with the date difference calculations in post #3

9. Registered User
Join Date
Jan 2004
Location
Australia
Posts
42
Hi,

I must say thank you for your effort as your code really help me solving the problem.

I tried the following and found it works to combine date and time field into a single datetime field.

unix_timestamp(concat(date_format(job.date, '%Y-%m-%d'), ' ', time_format(job.time, '%H:%i')))

10. SQL Consultant
Join Date
Apr 2002
Location