Convert EPOCH date to timestamp in oracle SQL-CodePudding

I get a date format EPOCH in database table - "19072"

First I need to convert to time- 21/03/2022 and concatenate 23:59:59 date conversion reference - https://www.timeanddate.com/date/dateadded.html?d1=1&m1=1&y1=1970&type=add&ay=&am=&aw=&ad=19072&rec=

Now I need to convert "21/03/2022 23:59:59" to UTC 7:00(Jakarta, Indonesia) Final expected output is - "22/03/2022 06:59:59"

CodePudding user response：

You can add a number of days directly to a date:

select date '1970-01-01'   19072 from dual;

21-MAR-22

or add an interval to a timestamp; which is probably more appropriate as you need to end up with a timestamp with time zone anyway, so this starts with a UTC value:

select timestamp '1970-01-01 00:00:00 UTC'   (19072 * interval '1' day) from dual;

21-MAR-22 00.00.00.000000000 PM UTC

Then you can add hours, minutes and seconds (or go ahead another day and subtract one second):

select 
  timestamp '1970-01-01 00:00:00 UTC'
      (19072 * interval '1' day)
      (23 * interval '1' hour)
      (59 * interval '1' minute)
      (59 * interval '1' second)
from dual;

21-MAR-22 11.59.59.000000000 PM UTC

and convert to your target time zone with at time zone:

select
  (
    timestamp '1970-01-01 00:00:00 UTC'
        (19072 * interval '1' day)
        (23 * interval '1' hour)
        (59 * interval '1' minute)
        (59 * interval '1' second)
  )
  at time zone 'Asia/Jakarta'
from dual;

22-MAR-22 06.59.59.000000000 AM ASIA/JAKARTA

and then for display purposes, convert to a string in the format you want:

select
  to_char(
    (
      timestamp '1970-01-01 00:00:00 UTC'
          (19072 * interval '1' day)
          (23 * interval '1' hour)
          (59 * interval '1' minute)
          (59 * interval '1' second)
    )
    at time zone 'Asia/Jakarta',
    'DD/MM/YYYY HH24:MI:SS'
  ) as result
from dual;

22/03/2022 06:59:59

You can simplify a bit by modifying your epoch, though it looks a bit odd:

select
  to_char(
    (
      timestamp '1970-01-01 23:59:59 UTC'
          (19072 * interval '1' day)
    )
    at time zone 'Asia/Jakarta',
    'DD/MM/YYYY HH24:MI:SS'
  ) as result
from dual;

22/03/2022 06:59:59

(I'd probably prefer to keep the usual epoch and do the extra explicit interval additions...)

db<>fiddle

Only format as a string to display it. If you need to pass the value somewhere else then leave it as a timestamp with time zone, or if necessary cast that to a plain timestamp or even a date.