Oracle - Convert Unixtime to local datetime including DST (Daylight Savings Time) and vice versa

Question

I have a NUMBER field (UTCSTAMP) with UnixTimestamps and I would like to have a (custom) function to easily return local datetime (Europe/Amsterdam) including DST (Daylight Savings Time). The output should be this format: 'yyyy-mm-dd hh24:mi:ss' For example: unix_ts2date_function(1576666800)

I found this article: Convert Unixtime to Datetime SQL (Oracle) but it doesn't take Daylight Savings Time into account.

I would also like to have a (custom) function to easily convert a local datetime (Europe/Amsterdam) including DST (Daylight Savings Time) to a UnixTimestamp and use it in a WHERE clause. For example:

SELECT * FROM table
WHERE UTCSTAMP > date2unix_ts_function('2019-05-01') (DST is active)
AND UTCSTAMP < date2unix_ts_function('2020-11-30') (DST not active)

I also found this article: Convert timestamp datatype into unix timestamp Oracle but it also doesn't take Daylight Savings Time into account.

Answer 1

Add a utcstamp seconds to the epoch 1970-01-01 UTC (as a TIMESTAMP data type) and then use AT TIME ZONE to convert it to your desired time zone:

Oracle Setup:

CREATE TABLE your_table ( utcstamp ) AS
SELECT 1576666800 FROM DUAL

Query:

SELECT ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utcstamp * INTERVAL '1' SECOND )
         AT TIME ZONE 'Europe/Amsterdam' AS Amsterdam_Time
FROM   your_table

Output:

| AMSTERDAM_TIME                                 |
| :--------------------------------------------- |
| 2019-12-18 12:00:00.000000000 EUROPE/AMSTERDAM |

Query 2:

If you want it as a DATE then just wrap everything in a CAST:

SELECT CAST(
         ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utcstamp * INTERVAL '1' SECOND )
           AT TIME ZONE 'Europe/Amsterdam'
         AS DATE
       ) AS Amsterdam_Time
FROM   your_table

Output:

| AMSTERDAM_TIME      |
| :------------------ |
| 2019-12-18 12:00:00 |

db<>fiddle here

---

CREATE FUNCTION DATE_TO_UTCEPOCHTIME (
  dt IN DATE,
  tz IN VARCHAR2 DEFAULT 'UTC'
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN ROUND(
           ( CAST( FROM_TZ( dt, tz ) AT TIME ZONE 'UTC' AS DATE )
             - DATE '1970-01-01' )
           * 24 * 60 * 60
         );
END;
/

CREATE FUNCTION UTCEPOCHTIME_TO_DATE(
  utctime IN NUMBER,
  tz      IN VARCHAR2 DEFAULT 'UTC'
) RETURN DATE DETERMINISTIC
IS
BEGIN
  RETURN ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utctime * INTERVAL '1' SECOND )
           AT TIME ZONE tz;
END;
/

then you can do:

SELECT utcepochtime_to_date( utcstamp, 'Europe/Amsterdam' )
FROM   your_table;

Which outputs:

| UTCEPOCHTIME_TO_DATE(UTCSTAMP,'EUROPE/AMSTERDAM') |
| :------------------------------------------------ |
| 2019-12-18 12:00:00                               |

and

SELECT date_to_utcepochtime(
         DATE '2019-12-18' + INTERVAL '12:00:00' HOUR TO SECOND,
         'Europe/Amsterdam'
       ) AS utcepochtime
FROM   DUAL;

which outputs:

| UTCEPOCHTIME |
| -----------: |
|   1576666800 |

db<>fiddle here