How do I get the unix epoch from Oracle date?

Question

Does anyone have a better AND faster solution on getting the unix epoch timestamp using Oracle SQL than this?:

SQL> select (cast(sysdate as date) - cast(to_date('1970-01-01', 'YYYY-MM-DD') as date)) * 86400 as unixepoch from dual;

 UNIXEPOCH
----------
1490789604;

Oneliner preferred ;)

Solution 1

The above answer is only correct if your database time zone is in UTC. Unix time is always in UTC. The correct answer that works on any database, regardless of configuration is:

--Convert current time to epoch.
select (cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400
from dual

--Convert hard-coded timestamp to epoch.
select (cast (timestamp '2019-12-31 23:59:59' at time zone 'UTC' as date) - date '1970-01-01') * 86400
from dual;

Solution 2

You do not need to cast the values as dates, since they are already dates.

SELECT ( SYSDATE - DATE '1970-01-01' ) * 86400 AS unixepoch
FROM   DUAL;

Comments

[Tobia]

This does not work for any timezone except UTC.

[Tobia]

What if I have a date column instead of the current time or a hard-coded timestamp?