Add 1 day to timezone aware timestamp with regards to daylight savings

Question

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.

Alex Poole · Answer 1 · 2021-06-26T10:27:57.570

You could cast the timestamp with time zone value to a plain timestamp, which discards the time zone information; then add the 1-day interval, and declare the result to be in the required time zone:

from_tz(cast(timestamp '2021-03-28 00:00:00 Europe/Berlin' as timestamp) + interval '1' day, 'Europe/Berlin') as plus_1_day

or cast to a date (which could be implicit), add a day, and cast back:

from_tz(cast(cast(timestamp '2021-03-28 00:00:00 Europe/Berlin' as date) + 1 as timestamp), 'Europe/Berlin')

Adapting your example and showing the intermediate values:

select timestamp '2021-03-28 00:00:00 Europe/Berlin' as before_dst,
       cast(timestamp '2021-03-28 00:00:00 Europe/Berlin' as timestamp) as as_ts,
       cast(timestamp '2021-03-28 00:00:00 Europe/Berlin' as timestamp) + 1 as plus_1_day_ts,
       from_tz(cast(timestamp '2021-03-28 00:00:00 Europe/Berlin' as timestamp) + interval '1' day, 'Europe/Berlin') as plus_1_day
from dual;

BEFORE_DST	AS_TS	PLUS_1_DAY_TS	PLUS_1_DAY
2021-03-28 00:00:00 +01:00	2021-03-28 00:00:00	2021-03-29 00:00:00	2021-03-29 00:00:00 +02:00

db<>fiddle

This assumes that you're always dealing with a fixed known time zone region; if you actually have a variable or column value with an unknown time zone then you can extract the region from that and use that as the from_tz() argument.

You should also be aware that this will work for your example at midnight, but won't work for all times. For example if your starting value was timestamp '2021-03-27 02:30:00 Europe/Berlin' then it would fail with "ORA-01878: specified field not found in datetime or interval", because it would end up try to declare 2021-03-28 02:30:00 to be in zone Europe/Berlin - and there is no such time, as that falls into the 'lost' hour of 02:00-03:00. Simply adding a day interval handles that - but then doesn't work as you expect in your example...

And this is because of this line in the documentation:

Oracle performs all timestamp arithmetic in UTC time.

2021-03-28 00:00:00 Europe/Berlin is 2021-03-27 23:00:00 UTC; adding a day to that is 2021-03-28 23:00:00 UTC; which is 2021-03-29 02:00:00 Europe/Berlin

Add 1 day to timezone aware timestamp with regards to daylight savings

1 Answers1