[SOLVED] How to add a months to a timestamp with time zone value while keeping the exact same hour

How to add a months to a timestamp with time zone value while keeping the exact same hour

I have a PL/SQL variable of type TIMESTAMP WITH TIME ZONE to whose value I want to add exactly one month. But especially the time should remain identical when changing from daylight saving time to standard time.

The following SELECT (although not PLS/SQL) shows the problem:

WITH
   data
   AS
      (    SELECT TO_TIMESTAMP_TZ('2023-04-17 05:00:00 EUROPE/BERLIN', 'yyyy-mm-dd hh24:mi:ss tzr') + NUMTOYMINTERVAL(LEVEL, 'MONTH') AS ts
             FROM DUAL
       CONNECT BY LEVEL <= 7)
SELECT to_char(ts, 'yyyy-mm-dd hh24:mi:ss tzr') AS ts
  FROM data;

This returns

2023-05-17 05:00:00 Europe/Berlin
2023-06-17 05:00:00 Europe/Berlin
2023-07-17 05:00:00 Europe/Berlin
2023-08-17 05:00:00 Europe/Berlin
2023-09-17 05:00:00 Europe/Berlin
2023-10-17 05:00:00 Europe/Berlin
2023-11-17 04:00:00 Europe/Berlin

As you can see the time changes to 4 o'clock in November.

Is there a function in Oracle to add a month without changing the time?

Solution

You can try this:

FROM_TZ(
   CAST(
      TO_TIMESTAMP_TZ('2023-04-17 05:00:00 EUROPE/BERLIN', 'yyyy-mm-dd hh24:mi:ss tzr') AS TIMESTAMP
   ) + NUMTOYMINTERVAL(LEVEL, 'MONTH'),
   'Europe/Berlin'
)

CAST(... AS TIMESTAMP) removes the time zone (and DST) information, then it adds the months and afterwards you attach time zone again with FROM_TZ