Convert epoch time to timestamp in oracle database with system timezone

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

1 answer

  • answered 2022-05-04 10:34 Wernfried Domscheit

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum