0

What's the best way to convert an epoch column to UTC in the format yyyy-mm-dd hh24:mi:ss in Oracle db (PL/SQL).

e.g. 1646670198000 --> 2022-03-07 4:23:18

Dante
  • 537
  • 2
  • 4
  • 18
  • A `TIMESTAMP WITH TIMEZONE` is a binary data type (and so is a `DATE`) and it is never stored with a particular format. If you want to format a timestamp then that is a display problem and not a storage problem. – MT0 Apr 29 '22 at 18:51

1 Answers1

1

The main consideration is the data type of the result. Epoch is a timestamp at time zone GMT; the data type should be timestamp with time zone.

If we can agree on this, then here's one way:

select timestamp '1970-01-01 00:00:00 GMT' 
         + 1646670198 * interval '1' second as ts
from   dual
;



TS                          
----------------------------
2022-03-07 16:23:18.000 GMT

I wrote it that way - with the epoch set off separately as a multiplying factor - so that you can replace it with a column name, a bind variable, or any other expression that evaluates to a valid epoch in seconds.