In MariaDB, TIME_TO_SEC() is a built-in date and time function that returns its time argument, converted to seconds.
Syntax
The syntax goes like this:
TIME_TO_SEC(time)Where time is the time value to be converted to seconds.
Example
Here’s an example:
SELECT TIME_TO_SEC('00:01:00');Result:
+-------------------------+
| TIME_TO_SEC('00:01:00') |
+-------------------------+
| 60 |
+-------------------------+
Here it is with a couple of other time values:
SELECT
TIME_TO_SEC('01:00:00'),
TIME_TO_SEC('15:37:46');Result:
+-------------------------+-------------------------+
| TIME_TO_SEC('01:00:00') | TIME_TO_SEC('15:37:46') |
+-------------------------+-------------------------+
| 3600 | 56266 |
+-------------------------+-------------------------+
Microseconds
TIME_TO_SEC() supports microseconds:
SELECT TIME_TO_SEC('00:01:00.123456');Result:
+--------------------------------+
| TIME_TO_SEC('00:01:00.123456') |
+--------------------------------+
| 60.123456 |
+--------------------------------+
Larger Hours
TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'.
Therefore, the hour portion can be much higher than 23:
SELECT TIME_TO_SEC('838:59:59');Result:
+--------------------------+
| TIME_TO_SEC('838:59:59') |
+--------------------------+
| 3020399 |
+--------------------------+
Negative Time Values
Here’s an example with a negative time value:
SELECT TIME_TO_SEC('-820:38:15');Result:
+---------------------------+
| TIME_TO_SEC('-820:38:15') |
+---------------------------+
| -2954295 |
+---------------------------+
Out of Range Times
However, providing a time value outside the range will return the seconds for the upper boundary of that range, along with a warning:
SELECT TIME_TO_SEC('920:38:15');Result:
+--------------------------+
| TIME_TO_SEC('920:38:15') |
+--------------------------+
| 3020399 |
+--------------------------+
1 row in set, 1 warning (0.002 sec)
Let’s check the warning:
SHOW WARNINGS;Result:
+---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1292 | Truncated incorrect time value: '920:38:15' | +---------+------+---------------------------------------------+
Invalid Argument
When passed any invalid arguments, TIME_TO_SEC() returns null with a warning:
SELECT TIME_TO_SEC('Homer');Result:
+----------------------+
| TIME_TO_SEC('Homer') |
+----------------------+
| NULL |
+----------------------+
1 row in set, 1 warning (0.000 sec)
Check the warning:
SHOW WARNINGS;Result:
+---------+------+-------------------------------+ | Level | Code | Message | +---------+------+-------------------------------+ | Warning | 1292 | Incorrect time value: 'Homer' | +---------+------+-------------------------------+
Missing Argument
Calling TIME_TO_SEC() with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT TIME_TO_SEC();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TIME_TO_SEC'
And another example:
SELECT TIME_TO_SEC('10:09:10', 2);Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TIME_TO_SEC'