Home » SQL & PL/SQL » SQL & PL/SQL » Select daylight saving values
icon1.gif  Select daylight saving values [message #211566] Fri, 29 December 2006 08:51 Go to next message
wolf2006
Messages: 1
Registered: December 2006
Location: Germany
Junior Member
Hello Users,
I have a problem ( to understand ) to select daylight saving values from a table.

I habe a table with a column TIMESTAMP(0) WITH LOCAL TIME ZONE.
In this column I save for example the values of 2007-10-29 ( the swtiching day in Germany from CEST to CET ) in UTC.
The dbtimezone is UTC.
On this day the hour between 02:00 and 03:00 doubles.

I use the follwing INSERT-statements :

INSERT... from_tz(cast(to_date('2006-10-29 02:30','YYYY-MM-DD HH24:MI:SS') AS timestamp), 'Europe/Berlin' ) AT time zone 'UTC' ....

The result of this INSERTS are :

INSERT -> convert to UTC

...to_date('2006-10-29 00:30'... -> 22:30 OK
...to_date('2006-10-29 00:45'... -> 22:45 OK
...to_date('2006-10-29 01:00'... -> 23:00 OK
...to_date('2006-10-29 01:15'... -> 23:15 OK
...to_date('2006-10-29 01:30'... -> 23:30 OK
...to_date('2006-10-29 01:45'... -> 23:45 OK

first hour 02:00-03:00
...to_date('2006-10-29 02:00'... -> 01:00 ????
...to_date('2006-10-29 02:15'... -> 01:15 ????
...to_date('2006-10-29 02:30'... -> 01:30 ????
...to_date('2006-10-29 02:45'... -> 01:45 ????

second hour 02:00-03:00
...to_date('2006-10-29 02:00'... -> 01:00 OK
...to_date('2006-10-29 02:15'... -> 01:15 OK
...to_date('2006-10-29 02:30'... -> 01:30 OK
...to_date('2006-10-29 02:45'... -> 01:45 OK

...to_date('2006-10-29 03:00'... -> 02:00 OK
...to_date('2006-10-29 03:15'... -> 02:15 OK
...to_date('2006-10-29 03:30'... -> 02:30 OK
...to_date('2006-10-29 03:45'... -> 02:45 OK

Where is is the UTC time from 00:00 to 00:45 ????
How can say then INSERT-statement that I mean the first or second doubles hour ??????


Thanks very much,

Happy new year 2007
Wolf

[Updated on: Fri, 29 December 2006 08:51]

Report message to a moderator

Re: Select daylight saving values [message #211913 is a reply to message #211566] Wed, 03 January 2007 01:36 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Good question - looks like a bug to me. For this, I would log a SR on Matalink so Oracle Support and explain how the algorithm works.

If you get a reply, please also update this post.
Previous Topic: Logon Trigger Issue
Next Topic: report quest.
Goto Forum:
  


Current Time: Sat Dec 03 16:14:13 CST 2016

Total time taken to generate the page: 0.11488 seconds