CODEWITHSUNDEEP
sqloracle-databasetimeoracle11gdst
alexvetter
alexvetter

Reputation: 1996

Timestamp calculation with daylight saving time

The Central European Daylight Saving Time begins on last Sunday in March. We set our clocks from 02:00 to 03:00. What happens if I do timestamp calcuations in a database request - lets say at 01:59?

UPDATE sessions SET aliveuntil = (CURRENT_TIMESTAMP + INTERVAL '1' MINUTE) WHERE id = ?

Do I get 03:00 as result or 02:00?

And what about the over way around if we set our clocks from 03:00 to 02:00?

SELECT id FROM sessions WHERE aliveuntil < (CURRENT_TIMESTAMP - INTERVAL '1' MINUTE)

After time changed from 03:00 to 02:00... what happens with the (CURRENT_TIMESTAMP - INTERVAL '1' MINUTE) at 02:00? Is it 02:59 or 01:59?

How should this be handled? Best practice and how it's handle (in my particular case) by Oracle Database 11g Release 11.2.0.2.0?

Upvotes: 6

Views: 2640

Answers (1)

sgeddes
sgeddes

Reputation: 62851

If I'm understanding their documentation correctly, it depends on how the table/columns are setup in the database. If the columns are setup to use WITH TIME ZONE, then Oracle automatically determines the correct/relevant values. In your above example if the column aliveuntil has this setting, then if you try to add 1 minute at 1:59, the time will update to 3:00.

Here is a helpful article I found on the subject:

http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

Scroll down to the bottom of the article and you should see what you're looking for.

Here was a section of the article I found relevant:

For example, in the Eastern region of the United States, the time changes from 01:59:59 a.m. to 3:00:00 a.m. when Daylight Saving Time goes into effect. The interval between 02:00:00 and 02:59:59 a.m. does not exist. Values in that interval are invalid.

Upvotes: 4

Related Questions