How TIME_TO_SEC() Works in MariaDB

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'