Oracle SQL convert number (that stores a timestamp) to human readable date time on select-CodePudding

I have a timestamp stored on a column called ts of type NUMBER(15, 0), and I want to print their corresponding human readable datetime in any human readable format, like '2022-03-15 23:08:24'.

None of what I have tried works, but the most closed thing is:

select 
   to_date('19700101', 'YYYYMMDD')   ( 1 / 24 / 60 / 60 / 1000) * ts
from my_table;

But this translates ts to a human readable date, not a datetime. I'm not able to show the hours, minutes and seconds. I think Oracle SQL has functions to translate timestamps to datetimes in a straightforward way, but it requires the timestamp is stored on a TIMESTAMP column, but in my case it's a NUMBER.

CodePudding user response：

You are generating a date, which retains the time to second precision, but loses the milliseconds. You're also ignoring the time zone your ts is nominally in, which is presumably UTC - as an epoch/Unix time.

Anyway, you can change how the date is displayed by changing your session settings, or with to_char():

select 
  to_char(
    date '1970-01-01'   (1 / 24 / 60 / 60 / 1000) * ts,
    'YYYY-MM-DD HH24:MI:SS'
  )
from my_table;

If you want to keep milliseconds, and preserve time zone, use a timestamp and intervals instead:

select
  to_char(
    timestamp '1970-01-01 00:00:00 UTC'   ((ts / 1000) * interval '1' second),
    'YYYY-MM-DD HH24:MI:SS.FF3 TZR'
  ) as string_result
from my_table;

With an example ts value of 1655977424456, that gives result 2022-06-23 09:43:44.456 UTC

The result is still UTC. You can also convert the time to a different time zone if that's useful; for example:

select
  to_char(
    (timestamp '1970-01-01 00:00:00 UTC'   ((ts / 1000) * interval '1' second))
      at time zone 'Europe/Madrid',
    'YYYY-MM-DD HH24:MI:SS.FF3'
  )
from my_table;

The same example ts value of 1655977424456 now gives 2022-06-23 11:43:44.456 EUROPE/MADRID, or just 2022-06-23 11:43:44.456 if you leave the TZR off the format model or convert to a plain timestamp.

And you should only convert to a string to display - not to store or manipulate or pass around the actual timestamp value.

db<>fiddle with some variations.