Timestamp conversion to EPOCH time in Oracle SQL-CodePudding

I have input date as a column in table "22/03/2022 06:59:59"

I need to convert into EPOCH time - Expected output- "1647932399" timezone(jakarta, Indonesia)

reference link- https://www.epochconverter.com/

time zone of input data is - UTC 7:00(Jakarta)

Tried with this-SQL CODE

SELECT (CAST(SYS_EXTRACT_UTC(to_timestamp('22/03/2022 06:59:59','dd/mm/yyyy HH:MI:SS' )) AS DATE) - TO_DATE('01/01/1970','DD/MM/YYYY')) * 24 * 60 * 60 FROM DUAL;

Result displayed is 1647907199

But didn't get the expected output

CodePudding user response：

1647932399 is the epoch for 2022-03-22 06:59:59 UTC. Your start time is 22-03-22 06:59:59 Asia/Jakarta, which is 22-03-21 23:59:59 UTC, and the epoch for that is 1647907199 - which is what you're getting. So your result is correct; but risky.

You are relying on implicit conversion. to_timestamp('22/03/2022 06:59:59','dd/mm/yyyy HH:MI:SS') gives you a plain timestamp, with no time zone information. When you then do SYS_EXTRACT_UTC(...) the plain timestamp is implicitly converted to a timestamp with time zone using your session time zone - which happens to be Jakarta, it seems. So it works - for you, in this session. If you change your session time zone, or more likely if someone else runs the same code from a session with a different time zone, then the reslut will be different.

To be more explicit, you can convert your string to a plain timestamp, then declare that it represents a Jakarta time, and then convert that to UTC:

sys_extract_utc(from_tz(to_timestamp('22/03/2022 06:59:59', 'DD/MM/YYYY HH24:MI:SS'), 'Asia/Jakarta'))

21-MAR-22 23.59.59.000000000 UTC

There are then basically two ways to convert that to an epoch number; either cast it to a date, subtract 1970-01-01 as a date, and manipulate the resulting number of days (as in your question):

select
  round(
    (
      cast(
        sys_extract_utc(from_tz(to_timestamp('22/03/2022 06:59:59', 'DD/MM/YYYY HH24:MI:SS'), 'Asia/Jakarta'))
          as date)
        - date '1970-01-01'
     ) * 24 * 60 * 60
  ) as result
from dual;

1647907199

Or leave it as a timestamp, subtract 1970-01-01 as a timestamp, and manipulate the resulting interval:

select
  (extract(day from diff) * 24 * 60 * 60)
    (extract(hour from diff) * 60 * 60)
    (extract(minute from diff) * 60)
    extract(second from diff)
  as result
from (
  select sys_extract_utc(from_tz(to_timestamp('22/03/2022 06:59:59', 'DD/MM/YYYY HH24:MI:SS'), 'Asia/Jakarta'))
    - timestamp '1970-01-01 00:00:00' as diff
  from dual
);

1647907199

Note that either way you can easily get the short result that was the basis for your previous question, by truncating the number or extracting just the days, without explicitly subtracting the 23:59:59 part. Either gives you 19072.

db<>fiddle