I am trying to add 1 day to a timezone aware timestamp.
In this example I expected + interval '1' day
to add 23 hours because DST starts on 2021-03-28 02:00:00
in Europe/Berlin
, but it behaves the same as + interval '24' hour
:
select timestamp '2021-03-28 00:00:00 Europe/Berlin' as before_dst,
timestamp '2021-03-28 00:00:00 Europe/Berlin' + interval '1' day as plus_1_day,
timestamp '2021-03-28 00:00:00 Europe/Berlin' + interval '24' hour as plus_24_hour
from dual;
BEFORE_DST | PLUS_1_DAY | PLUS_24_HOUR |
---|---|---|
2021-03-28 00:00:00.000000000 +01:00 | 2021-03-29 01:00:00.000000000 +02:00 | 2021-03-29 01:00:00.000000000 +02:00 |
Is there a way to add a day to a timestamp so that the beginnings or ends of daylight saving times are respected? For the example above that means a way to have oracle automatically recognize that the day 2021-03-28
only has 23 hours in Europe/Berlin
.
I attempted to solve this by converting the timestamp to a local timestamp using at local
before adding a day, but that does not work because at local
converts the timestamp to the local time zone and not to something like a LocalDateTime
in java, resulting in the exact same outcome: + interval '1' day
always adding exactly 24 hours.