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, DERBY, DUCKDB, EXASOL, FIREBIRD, INFORMIX, REDSHIFT, SNOWFLAKE, TERADATA, TRINO, VERTICA, YUGABYTEDB
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!