1

I was working with SQL and I found something unusual. I was calculating the time difference between two datetimes in h:m:s format using SQL

select time_format(timediff(
    '2015-09-12 18:47:15',
    '2015-09-12 18:38:33'),'%h:%i:%s') as time_down

This returns 12:08:39 which is incorrect and it should be 00:08:39

select time_format(timediff(
    '2015-09-12 18:10:04',
    '2015-09-12 16:34:05'),'%h:%i:%s') as time_down

This returns 1:35:59 which is correct

But when I changed %h to %H the result of the first query was correct.

What is the explanation?

  • What are you trying to get to with time_down? Is it possible that it would be more than 12 hours? What happens if it spans "midnight" of a particular day, as timediff() won't take the date (only the time) into account.....? – seventyeightist Sep 12 '15 at 14:24
2

Your situation is simple. You're trying to format a time result that is 00:08:42 and according with the MySQL date and time functions, %h for Hour (01..12), you're converting 00(hour) to 12. If you change it for %H you will get the desired result %H for Hour (00..23).

Try this test:

mysql> select
    -> timediff('2015-09-12 18:47:15','2015-09-12 18:38:33') as timediffA,
    -> timediff('2015-09-12 18:10:04','2015-09-12 16:34:05') as timediffB,
    -> time_format(timediff('2015-09-12 18:47:15','2015-09-12 18:38:33'),'%h:%i:%s') as time_A_wtimeformath,
    -> DATE_FORMAT(timediff('2015-09-12 18:47:15','2015-09-12 18:38:33'),'%h:%i:%s') as time_A_wdateformath, 
    -> DATE_FORMAT(timediff('2015-09-12 18:47:15','2015-09-12 18:38:33'),'%H:%i:%s') as time_A_wdateformatH, 
    -> time_format(timediff('2015-09-12 18:10:04','2015-09-12 16:34:05'),'%h:%i:%s') as time_B_wtimeformath,
    -> DATE_FORMAT(timediff('2015-09-12 18:10:04','2015-09-12 16:34:05'),'%h:%i:%s') as time_B_wdateformath,
    -> DATE_FORMAT(timediff('2015-09-12 18:10:04','2015-09-12 16:34:05'),'%H:%i:%s') as time_B_wdateformatH;
+-----------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| timediffA | timediffB | time_A_wtimeformath | time_A_wdateformath | time_A_wdateformatH | time_B_wtimeformath | time_B_wdateformath | time_B_wdateformatH |
+-----------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 00:08:42  | 01:35:59  | 12:08:42            | 12:08:42            | 00:08:42            | 01:35:59            | 01:35:59            | 01:35:59            |
+-----------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0,01 sec)

mysql> 

Try it in SQLFiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.