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, HSQLDB, MariaDB, MySQL
UNIX_TIMESTAMP(TIMESTAMP '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')
MemSQL
UNIX_TIMESTAMP({ts '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 */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!