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'