Convert epoch time to timestamp in oracle database with system timezone

Question

Can someone please suggest me how to convert epoch time to timestamp in oracle including the database timezone. I am currently using below code:

cast ( TO_TIMESTAMP_TZ('1970-01-01 00:00:00.0 UTC', 'YYYY-MM-DD HH24:MI:SS.FF TZR') + NUMTODSINTERVAL(value/1000, 'SECOND') as timestamp with local time zone)

But it appears this is converting to the sessiontimezone. I need it to be converted to dbtimezone

(In my case select dbtimezone gives PST time and select sessiontimezone gives Asia/Calcutta time.)

Wernfried Domscheit · Accepted Answer · 2022-05-04T11:26:42.187

1

Data type timestamp with local time zone always shows date/time at SESSIONTIMEZONE

Try

(TO_TIMESTAMP_TZ('1970-01-01 00:00:00.0 UTC', 'YYYY-MM-DD HH24:MI:SS.FF TZR') + NUMTODSINTERVAL(value/1000, 'SECOND')) AT TIME ZONE DBTIMEZONE

Or with literals:

(Timestamp '1970-01-01 00:00:00 UTC' + value/1000 * INTERVAL '1' SECOND) AT TIME ZONE DBTIMEZONE

edited May 04 '22 at 11:26

answered May 04 '22 at 10:34

Wernfried Domscheit

54,457
9
76
110

hi @Wernfried Tried using AT TIME ZONE DBTIMEZONE earlier but getting syntax error at DBTIMEZONE "ORA-00905: missing keyword" – Somtirtha Ghosh May 04 '22 at 10:47
also, is it possible to convert to SYSDATE or SYSTIMESTAMP instead of DBTIMEZONE? – Somtirtha Ghosh May 04 '22 at 11:23
You must put it in brackets `(TIMESTAMP ... + NUMTODSINTERVAL ...) AT TIME ZONE DBTIMEZONE` – Wernfried Domscheit May 04 '22 at 11:23
Yes, would be `... AT TIME ZONE TO_CHAR(SYSTIMESTAMP, 'tzr')`. See https://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926 – Wernfried Domscheit May 04 '22 at 11:24
thanks a lot, the SYSTIMESTAMP worked – Somtirtha Ghosh May 04 '22 at 11:29

Convert epoch time to timestamp in oracle database with system timezone

1 Answers1