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

CodePudding user response：

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