Skip to content

TO_SECONDS()

Function Description

The TO_SECONDS(expr) function is used to calculate the difference in seconds between a given date or date time expr and January 1, 0 AD 0:0:00. If expr is NULL, NULL is returned.

Note

The 0000-00-00 and 0000-01-01 dates themselves are considered invalid. MatrixOne year query should start from 0001. Query 0000-00-00 and 0000-01-01, TO_SECONDS() returns the error message:

mysql> SELECT TO_SECONDS('0000-00-00');
ERROR 20301 (HY000): invalid input: invalid datatime value 0000-00-00
mysql> SELECT TO_SECONDS('0000-01-01');
ERROR 20301 (HY000): invalid input: invalid datatime value 0000-01-01

Similar to the TO_DAYS() function, for example, querying SELECT TO_SECONDS('08-10-07');, MatrixOne automatically completes year 08 to 0008, unlike MySQL. For more information, see Double-digit Year in Date.

Function Syntax

> TO_SECONDS(expr)

expr is a date-time value, which can be of type DATETIME, DATE or TIMESTAMP.

Example

mysql> SELECT TO_SECONDS('0001-01-01');
+-----------------------------+
| to_seconds(0001-01-01) |
+-----------------------------+
| 31622400 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_SECONDS('2023-07-12 08:30:00');
+---------------------------------------+
| to_seconds(2023-07-12 08:30:00) |
+---------------------------------------+
| 63856369800 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_SECONDS('2007-10-07');
+-----------------------------+
| to_seconds(2007-10-07) |
+-----------------------------+
| 63358934400 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_SECONDS('97-10-07');
+--------------------------+
| to_seconds(97-10-07) |
+--------------------------+
| 3085257600 |
+--------------------------+
1 row in set (0.00 sec)