Converting Unix epoch to date/timestamp with time zone in Oracle SQL

Question

I'm trying to write SQL expression that will take a unix epoch (seconds since 1970/1/1) to local time in specific time zone and extract the hour value from it. After researching solutions along this time, I'm still not sure how to deal with the time zone. Note that I try to avoid using the NEW_TIME() function as it takes only a limited subset of time zone acronyms and not the full time zone name.

select 
(TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + numtodsinterval(1464820200,'second')) as ts_utc,
(TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'America/Los_Angeles' + numtodsinterval(1464820200,'second')) as ts_la,
(DATE '1970-01-01' + numtodsinterval(1464820200,'second')) as date_utc,
FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' as date_ny,
FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/Los_Angeles' as date_la, -- this value is correct
EXTRACT(hour from FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York') as hour,
EXTRACT(TIMEZONE_OFFSET from FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York') as tz_offset,
NEW_TIME((DATE '1970-01-01' + numtodsinterval(1464820200,'second')), 'GMT', 'EDT') as date_edt -- this value is correct
from dual;

and the results are

TS_UTC  TS_LA   DATE_UTC    DATE_NY DATE_LA HOUR    TZ_OFFSET   DATE_EDT
2016-06-01 23:30:00.0   2016-06-01 23:30:00.0   2016-06-01 22:30:00.0   2016-06-01 15:30:00.0   2016-06-01 15:30:00.0   22  <UnknownType (-104)>    2016-06-01 18:30:00.0

There are several problems with the results

1. column 1: start with the timestamp, the value is off by 1 hour than using the DATE
2. column 2: creating this timestamp in America/Los_Angeles produces the same value as in UTC
3. column 4: converting the timestamp to America/New_York produces the same value as in America/Los_Angeles
4. column 6: the hour extracted is the UTC hour of the column 3
5. column 7: the timezone offset cannot be extracted as all, resulting in Unknown Type

What would be the correct Oracle SQL to do this?

Answer 1

I came up with a more or less kackish solution -- use the TO_CHAR() function to convert it to string first and then convert the string to number.

TO_NUMBER(TO_CHAR(FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'HH24')) as date_ny_int

Also the FROM_TZ() AT TIME ZONE work as expected, but the SQL tools (even the JDBC based tool) will output a string localized to the local time zone; so one should convert to string output with TO_CHAR() as well.

Answer 2

1. I ran your code, on my machine it shows 22:30 PM in column 1, not sure why you get 23:30 PM (actually I don't believe you). Indeed, you may wonder why you don't get the same answer (22:30 PM) in column 2; this is because named time zones include an adjustment for daylight saving time, which is NOT part of the UTC convention (so the first column is not affected).

2. and 3. Adding seconds to a timestamp will give you a new timestamp. The fact that the number of seconds is a "UNIX epoch" in your mind has no meaning; how is SQL supposed to know you wanted to translate/convert from UTC to whatever time zone you intended?

3. See my answer to 1. The hour extracted is 22, which is correct.

4. On my machine the time zone offset was extracted perfectly fine, at -4 hours.

What version of Oracle do you use?

Answer 3

I've posted here some methods to convert nanoseconds to timestamp and timestamp to nanoseconds. These methods are not affected by time zones and have a nanosecond precision.

You need to put your time zone where you see '-06:00'.

SELECT (TIMESTAMP '1970-01-01 00:00:00 UTC' + numtodsinterval(
    1598434427263 --Replace line with desired milliseconds
/ 1000, 'SECOND')) AT TIME ZONE '-06:00' AS TIMESTAMP FROM dual;

TIMESTAMP
26/08/20 09:33:47,263000000 UTC