Extract EPOCH from DateTime of varchar type in Oracle

Question

Postgres query:

SELECT extract(EPOCH FROM TIMESTAMP '2022-05-08T16:49:34Z') from table_name;

I need to convert the above Postgres query to an Oracle query.

I am new to Oracle and what I could come up with is,

SELECT (TO_TIMESTAMP('2022-05-08T16:49:34Z', 'yyyy-MM-dd"T"HH:mm:ssXXX')*24*3600) FROM DUAL;

but I am getting error,

ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

In Oracle, is there any reliable method to extract the EPOCH from the given DateTime(varchar)?

Will your timestamps ever include fractional seconds, and if so, do you need them preserved in the result? — , May 08 '22 at 17:31
To whoever closed this question: **Brilliant!** Do you understand that extracting epoch from a timestamp is not necessarily the same as extracting epoch from a date? In Oracle a timestamp may have fractional seconds, and if those must be preserved, there is absolutely nothing in an answer regarding "dates to epoch" that will help with this additional complication. "Community" (whoever that is) should consider reopening this question - or link to a relevant thread, if there is one. — , May 08 '22 at 17:44
Sorry for the late reply. I totally understand what you are saying. Regarding fractional seconds, actually, there is no reason to exclude that. Can you guide me on how I do that? Thanks — user15006167, May 08 '22 at 18:09

score 1 · Answer 1 · edited May 08 '22 at 17:24

1

You can try the below as mentioned at : https://www.epochconverter.com/

Oracle PL/SQL:   
SELECT (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) -
TO_DATE('01/01/1970','DD/MM/YYYY')) * 24 * 60 * 60 FROM DUAL;

edited May 08 '22 at 17:24

MatBailie

answered May 08 '22 at 17:23

Crack_it

score 0 · Answer 2 · answered May 08 '22 at 17:15

0

(trunc(TO_TIMESTAMP('2022-05-08T16:49:34Z', 'yyyy-MM-dd"T"HH:mm:ssXXX')) - TO_DATE('01/01/1970', 'MM/DD/YYYY')) * 24 * 60 * 60

answered May 08 '22 at 17:15

Vladimir.V.Bvn

ORA-01810: format code appears twice. I am getting this error. – user15006167 May 08 '22 at 17:36
select (trunc(TO_TIMESTAMP('2022-05-08T16:49:34Z', 'yyyy-MM-dd"T"HH:mm:ssXXX')) - TO_DATE('01/01/1970', 'MM/DD/YYYY')) * 24 * 60 * 60 from dual; – user15006167 May 08 '22 at 17:37

2 Answers2