Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12

EPOCH

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Extract the EPOCH value from a datetime value, i.e. the number of seconds since 1970-01-01 00:00:00 UTC.

The EPOCH function is a short version of the EXTRACT, passing a DatePart.EPOCH value as an argument.

SELECT epoch(TIMESTAMP '1970-01-01 00:00:15');
create.select(epoch(Timestamp.valueOf("1970-01-01 00:00:15"))).fetch();

The result being

+-------+
| epoch |
+-------+
|    15 |
+-------+

Dialect support

This example using jOOQ:

epoch(Timestamp.valueOf("1970-01-01 00:00:15"))

Translates to the following dialect specific expressions:

ASE, Sybase

datediff(ss, '1970-01-01 00:00:00', '1970-01-01 00:00:15.0')

Aurora MySQL, MemSQL, MySQL

UNIX_TIMESTAMP({ts '1970-01-01 00:00:15.0'})

Aurora Postgres, CockroachDB, DB2, H2, Postgres

extract(EPOCH FROM TIMESTAMP '1970-01-01 00:00:15.0')

Hana

seconds_between('1970-01-01', TIMESTAMP '1970-01-01 00:00:15.0')

HSQLDB, MariaDB

UNIX_TIMESTAMP(TIMESTAMP '1970-01-01 00:00:15.0')

Oracle

trunc((CAST(TIMESTAMP '1970-01-01 00:00:15.0' AS date) - DATE '1970-01-01') * 86400)

SQLDataWarehouse, SQLServer

datediff(ss, '1970-01-01 00:00:00', CAST('1970-01-01 00:00:15.0' AS DATETIME2))

SQLite

CAST(strftime('%s', '1970-01-01 00:00:15.0') AS int)

Access, BigQuery, ClickHouse, Derby, DuckDB, Exasol, Firebird, Informix, Redshift, Snowflake, Teradata, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo