How TO_SECONDS() Works in MariaDB

In MariaDB, TO_SECONDS() is a built-in date and time function that returns the number of seconds from year 0 to the given date or datetime expression.

Syntax

The syntax goes like this:

TO_SECONDS(expr)

Where expr is a date or datetime value.

Example

Here’s an example:

SELECT TO_SECONDS('2020-10-30');

Result:

+--------------------------+
| TO_SECONDS('2020-10-30') |
+--------------------------+
|              63771235200 |
+--------------------------+

Here it is with a couple of other time values:

SELECT 
    TO_SECONDS('0000-01-01'),
    TO_SECONDS('1234-10-30');

Result:

+--------------------------+--------------------------+
| TO_SECONDS('0000-01-01') | TO_SECONDS('1234-10-30') |
+--------------------------+--------------------------+
|                    86400 |              38967436800 |
+--------------------------+--------------------------+

Datetime Values

Here’s an example that uses a datetime value:

SELECT TO_SECONDS('2020-10-30 10:23:47');

Result:

+-----------------------------------+
| TO_SECONDS('2020-10-30 10:23:47') |
+-----------------------------------+
|                       63771272627 |
+-----------------------------------+

Microseconds

TO_SECONDS() ignores microseconds:

SELECT TO_SECONDS('2020-10-30 10:23:47.999999');

Result:

+------------------------------------------+
| TO_SECONDS('2020-10-30 10:23:47.999999') |
+------------------------------------------+
|                              63771272627 |
+------------------------------------------+

Numeric Dates

Numeric dates are supported:

SELECT TO_SECONDS(20201030);

Result:

+----------------------+
| TO_SECONDS(20201030) |
+----------------------+
|          63771235200 |
+----------------------+

Current Date/Time

Here’s an example that uses NOW() to return the number of seconds based on the current date and time:

SELECT 
    NOW(),
    TO_SECONDS(NOW());

Result:

+---------------------+-------------------+
| NOW()               | TO_SECONDS(NOW()) |
+---------------------+-------------------+
| 2021-05-31 09:34:16 |       63789672856 |
+---------------------+-------------------+

However, if we use CURDATE(), we’ll get a different result (because CURDATE() returns a date value, whereas NOW() returns a datetime value).

SELECT 
    NOW(),
    TO_SECONDS(NOW()),
    CURDATE(),
    TO_SECONDS(CURDATE());

Result (using vertical output):

                NOW(): 2021-05-31 09:35:01
    TO_SECONDS(NOW()): 63789672901
            CURDATE(): 2021-05-31
TO_SECONDS(CURDATE()): 63789638400

Invalid Argument

When passed any invalid arguments, TO_SECONDS() returns null with a warning:

SELECT TO_SECONDS('Homer');

Result:

+---------------------+
| TO_SECONDS('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 TO_SECONDS() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT TO_SECONDS();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TO_SECONDS'

And another example:

SELECT TO_SECONDS('2020-10-08', '10:09:10');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TO_SECONDS'