New versions: Dev (3.14) | Latest (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
strftime('%s', '1970-01-01 00:00:15.0')

-- ACCESS, CUBRID, DERBY, FIREBIRD, INFORMIX, INGRES, REDSHIFT, TERADATA, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.14, see #10141)

The jOOQ Logo