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 ;)

I've posted [here](https://stackoverflow.com/a/63597872/6866545) some methods to convert timestamp to nanoseconds and nanoseconds to timestamp. These methods are not affected by time zones and have a nanosecond precision. — Danton Estevam Pinto, Aug 26 '20 at 14:17

score 4 · Answer 1 · answered Mar 29 '17 at 10:29

4

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;

answered Mar 29 '17 at 10:29

MT0

143,790
11
59
117

This does not work for any timezone except UTC. – Tobia Nov 18 '21 at 16:49

score 4 · Answer 2 · edited Dec 31 '19 at 22:18

4

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;

edited Dec 31 '19 at 22:18

Jon Heller

34,999
6
74
132

answered Feb 04 '18 at 05:57

Stefan

99
1
5

What if I have a date column instead of the current time or a hard-coded timestamp? – Tobia Nov 18 '21 at 16:53

How do I get the unix epoch from Oracle date?

2 Answers2

Linked