MATH000 MATH000 - 7 months ago 13
SQL Question

MySQL TIME type having hours>23

How I can force MySQL display

TIME
columns data from
3.07:10:10
to
79:10:10
?

So the query
SELECT item FROM table_
should return the
TIME
in the
79:10:10
format.

edit:
TIME
stores hours, from '-838:59:59' to '838:59:59'.

I tried
SELECT TIME_FORMAT('3.07:10:10', '%H:%i:%s')
and unexpectedly it returns
00:00:03

Answer

(I blamed the OP because I thought TIME columns can only store up to 24 hrs. That's not true: They can store up to ±838 hours. Sorry & thanks for that.)

To insert/use days in TIME columns, use the following syntax:

mysql> SELECT TIME('3 07:10:10');
+--------------------+
| TIME('3 07:10:10') |
+--------------------+
| 79:10:10           |
+--------------------+
1 row in set (0.00 sec)

That is: replace the dot in 3.07:10:10 with a blank.

From the docs:

MySQL recognizes TIME values in these formats:

  • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34.
  • ...

If you insert '3 07:10:10' into a TIME column, it will automatically give 79:10:10 on select.