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.