In MySQL, the TO_SECONDS() function returns the number of seconds since the year 0.

This function is not to be confused with the TIME_TO_SECONDS() function, which returns the number of seconds in a given time value provided as an argument.

Syntax

The syntax goes like this:

TO_SECONDS(expr)

Where expr is a date or datetime value (to be compared to year 0).

Example 1 – Using a ‘date’ Argument

Here’s an example using a date argument.

SELECT TO_SECONDS('2021-09-21');

Result:

+--------------------------+
| TO_SECONDS('2021-09-21') |
+--------------------------+
|              63799401600 |
+--------------------------+

Example 2 – Using a ‘datetime’ Argument

Here’s an example using a datetime argument.

SELECT TO_SECONDS('2021-09-21 10:30:25');

Result:

+-----------------------------------+
| TO_SECONDS('2021-09-21 10:30:25') |
+-----------------------------------+
|                       63799439425 |
+-----------------------------------+

Example 3 – Using the Current Date

In this example, I pass the CURDATE() function as the argument in order to use the current date.

SELECT TO_SECONDS(CURDATE()) AS 'Result';

Result:

+-------------+
| Result      |
+-------------+
| 63697968000 |
+-------------+

Example 4 – Using the Current Date and Time

In this example, I pass the NOW() function as the argument in order to use the current date and time.

SELECT TO_SECONDS(NOW()) AS 'Result';

Result:

+-------------+
| Result      |
+-------------+
| 63698002698 |
+-------------+

Two-Digit Years

MySQL has special rules for dealing with dates with two-digit years. Two-digit years are ambiguous because the century is unknown. Basically, the following rules apply:

• Year values in the range 00-69 are converted to 2000-2069.
• Year values in the range 70-99 are converted to 1970-1999.

For a full explanation, see the MySQL documentation on how MySQL deals with two-digit years.

Here’s an example to demonstrate:

SELECT 
  TO_SECONDS('69-10-07') AS '69 (2069)',
  TO_SECONDS('70-10-07') AS '70 (1970)';

Result:

+-------------+-------------+
| 69 (2069)   | 70 (1970)   |
+-------------+-------------+
| 65315548800 | 62191324800 |
+-------------+-------------+

Abbreviated Dates

You can also use abbreviated dates. Here’s an example using the previous date values in abbreviated form.

SELECT 
  TO_SECONDS('691007') AS '69 (2069)',
  TO_SECONDS('701007') AS '70 (1970)';

Result:

+-------------+-------------+
| 69 (2069)   | 70 (1970)   |
+-------------+-------------+
| 65315548800 | 62191324800 |
+-------------+-------------+

Earlier Dates

The MySQL documentation warns that the TO_SECONDS() function:

is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable.