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.

Mostly a duplicate of https://stackoverflow.com/questions/43066665/how-to-convert-timestamp-with-milliseconds-to-date-in-oracle/43066880#43066880 but just with added time-zone conversion afterwards. — MT0, Dec 18 '19 at 11:15

MT0 · Accepted Answer · 2019-12-21T11:47:03.700

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

Thank you for your answer, but I'm asking for two functions: `unix_ts2date_function()` and `date2unix_ts_function()`. How would you wrap your code into these functions? — Matt, Dec 21 '19 at 11:10
Thank you! I'd like to use the function: `date_to_utcepochtime` like: `date_to_utcepochtime('2019-12-18 16:15:00')` having the default timezone set to `'Europe/Amsterdam'`. So without: `DATE '2019-12-18' + INTERVAL '12:00:00' HOUR TO SECOND, 'Europe/Amsterdam'`. Is that possible? — Matt, Dec 21 '19 at 14:48
Set the default value to `'Europe/Amsterdam'` rather than `'UTC'` and don't pass dates as strings; use the correct data type, a date. You can use `DATE '2019-12-18' + INTERVAL '12:00:00' HOUR TO SECOND` or `TO_DATE( '2019-12-18 16:15:00', 'YYYY-MM-DD HH24:MI:SS' )` or even `TIMESTAMP '2019-12-18 16:15:00'`. If you really must pass a string (don't) then just use `TO_DATE` in the function. — MT0, Dec 21 '19 at 21:57
Thank you MT0. I've managed to create both functions with your support. Much appreciated — Matt, Dec 22 '19 at 09:44

score 1 · Answer 2 · answered Dec 18 '19 at 11:21

You can achieve it using the following query where we have used FROM_TZ to convert UTC to Europe/Amsterdam time:

SQL> SELECT
  2      TO_CHAR(FROM_TZ( CAST( DATE '1970-01-01' + 1576666800/(24*60*60) AS TIMESTAMP ), 'UTC' )
  3             AT TIME ZONE 'Europe/Amsterdam', 'yyyy-mm-dd hh24:mi:ss') as "desired_date"
  4  FROM
  5      DUAL;

desired_date
-------------------
2019-12-18 12:00:00

SQL>

Daylight saving is automatically handled when we use the long name of the timezone which is 'Europe/Amsterdam' in our case.

Cheers!!

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

2 Answers2