Return the Unix Timestamp in SQL

Below are examples of using some of the more popular RDBMSs to return the Unix timestamp.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

SQL Server

In SQL Server, we can use the DATEDIFF() function, along with the GETUTCDATE() function:

SELECT DATEDIFF( second, '1970-01-01', GETUTCDATE() );

Result:

1650321073

We can also use s or ss instead of second.

Here, we get the difference between 1970-01-01 00:00:00 and the current UTC date/time. The GETUTCDATE() function returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time).

We could replace GETUTCDATE() with a specific date/time value in order to get the Unix timestamp based on that value.

Oracle Database

In Oracle Database, we can do the following:

SELECT (CAST (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400
FROM DUAL;

Example result:

1650253093.999999999999999999999999999998

We can also pass a specific date/time value to get the Unix timestamp based on that value:

SELECT (
    CAST(timestamp '2030-08-15 18:30:45' at time zone 'UTC' as date) - date '1970-01-01'
    ) * 86400
FROM DUAL;

Result:

1913074244.999999999999999999999999999997

MySQL

In MySQL, we can use the UNIX_TIMESTAMP() function, which is designed specifically to return the Unix timestamp:

SELECT UNIX_TIMESTAMP();

Result:

1650322036

We can also pass a specific time value to get the Unix timestamp based on that value:

SELECT UNIX_TIMESTAMP('2027-12-20');

Result:

1829260800

MariaDB

MariaDB has its own UNIX_TIMESTAMP() function for returning the Unix timestamp:

SELECT UNIX_TIMESTAMP();

Result:

1650322378

We can also pass a specific time value to get the Unix timestamp based on that value:

SELECT UNIX_TIMESTAMP('1970-01-02');

Result:

PostgreSQL

Here’s how we can do it in PostgreSQL:

SELECT extract(epoch from now());

Result:

1650152298.430101

And with a specific date value:

SELECT extract(epoch from date '2030-08-15');

Result:

1912982400

We can also get the Unix timestamp based on timestamp values, timestamp with time zone values, and even interval values. See Return the Unix Timestamp in PostgreSQL for examples.

SQLite

SQLite provides us with two options for the occasion:

The UNIXEPOCH() function is specifically designed to return the a unix timestamp:

SELECT UNIXEPOCH();

Result:

1646695411

The UNIXEPOCH() function was introduced in SQLite 3.38.0 (released 2nd February 2022), so it will only work if you’re using SQLite 3.38.0 or higher.

Another way to do it is with the STRFTIME() function. In this case, we pass %s as the argument:

SELECT STRFTIME('%s');

Result:

1646695406