Add a number of days to a time stamp with time zone while keeping the hour

24 Views Asked by At

Is it possible in Oracle to add a defined number of days to a time stamp with time zone without changing the time when switching from standard time to daylight saving time?

The following SELECT Statement show the issue:

WITH
   days
   AS
      (    SELECT LEVEL AS n
             FROM DUAL
       CONNECT BY LEVEL <= 7)
SELECT days.n,
         TO_TIMESTAMP_TZ('2024-03-27 03:00:00 Europe/Berlin', 'yyyy-mm-dd hh24:mi:ss TZR')
       + NUMTODSINTERVAL(n, 'DAY') AS ts
  FROM days;

It returns

N TS
1 2024-03-28 03:00:00,000000000 +01:00
2 2024-03-29 03:00:00,000000000 +01:00
3 2024-03-30 03:00:00,000000000 +01:00
4 2024-03-31 04:00:00,000000000 +02:00
5 2024-04-01 04:00:00,000000000 +02:00
6 2024-04-02 04:00:00,000000000 +02:00
7 2024-04-03 04:00:00,000000000 +02:00

Starting with N = 4 the hour changes from 3 to 4.

Doing the same in Postgres with

WITH
   days
   AS
      (SELECT generate_series(1, 7) as n)
SELECT days.n,
         timestamp with time zone '2024-03-27 03:00:00 Europe/Berlin'
       + interval '1 day' * n AS ts
  FROM days;

returns

N TS
1 2024-03-28 03:00:00.000000 +01:00
2 2024-03-29 03:00:00.000000 +01:00
3 2024-03-30 03:00:00.000000 +01:00
4 2024-03-31 03:00:00.000000 +02:00
5 2024-04-01 03:00:00.000000 +02:00
6 2024-04-02 03:00:00.000000 +02:00
7 2024-04-03 03:00:00.000000 +02:00

This is what I would expect to get.

0

There are 0 best solutions below