Postgres query:
SELECT extract(EPOCH FROM TIMESTAMP '2022-05-08T16:49:34Z') from table_name;
我需要将上面的Postgres查询转换为Oracle查询。
我是Oracle的新手,我能想到的是,
SELECT (TO_TIMESTAMP('2022-05-08T16:49:34Z', 'yyyy-MM-dd"T"HH:mm:ssXXX')*24*3600) FROM DUAL;
但是我得到错误,
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
在Oracle中,是否有任何可靠的方法从给定的DateTime(varchar)中提取EPOCH ?
您可以尝试如下所述: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;
(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