Home » SQL & PL/SQL » SQL & PL/SQL » Daylight Savings time
Daylight Savings time [message #257158] Tue, 07 August 2007 12:08 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
We are using Oracle 10.2.0.1.

We have an hourly table that has a TIMESTAMP(6) column containing timestamp values that represent UTC timestamps.

I am having trouble converting historical UTC timestamp data to 'US/Central' (i.e. either CST or CDT depending on the historical UTC timestamp.

Our DBA has said that the Oracle patch has been applied for the new Daylight Savings dates. However, the following code:

select  FROM_TZ( timestamp '2007-03-11 06:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-03-11 07:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-03-11 08:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-03-11 09:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;

select  FROM_TZ( timestamp '2007-04-01 06:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-04-01 07:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-04-01 08:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-04-01 09:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;

select  FROM_TZ( timestamp '2007-10-28 05:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-10-28 06:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-10-28 07:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-10-28 08:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
      
select  FROM_TZ( timestamp '2007-11-04 05:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-11-04 06:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-11-04 07:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;
select  FROM_TZ( timestamp '2007-11-04 08:00:00', '00:00' ) AT TIME ZONE 'US/Central'  from dual;


produces:

3/11/2007 12:00:00.000 AM
3/11/2007 1:00:00.000 AM
3/11/2007 2:00:00.000 AM
3/11/2007 3:00:00.000 AM

4/1/2007 12:00:00.000 AM
4/1/2007 1:00:00.000 AM
4/1/2007 3:00:00.000 AM
4/1/2007 4:00:00.000 AM

10/28/2007 12:00:00.000 AM
10/28/2007 1:00:00.000 AM
10/28/2007 1:00:00.000 AM
10/28/2007 2:00:00.000 AM

11/3/2007 11:00:00.000 PM
11/4/2007 12:00:00.000 AM
11/4/2007 1:00:00.000 AM
11/4/2007 2:00:00.000 AM


The 2 new Daylight Savings dates for 2007 are March 11 and November 4. The 2 old Daylight Savings dates for 2007 were April 1 and October 28.

Based on the results, my code above is obviously triggering something that uses the old Daylight Savings dates for 2007. Either I am doing something wrong, or maybe the patch was not applied.

I have searched the forum and tried other solutions. new_time() will not work since you have to specify either CST or CDT and this is not known for the historical hourly data (i.e. if I run a query on November 5, 2007 to retrieve all the hourly data for the last 30 days, then I want to see the Daylight Savings effect for November 4, not October 28).

Thanks for your assistance.

Re: Daylight Savings time [message #257159 is a reply to message #257158] Tue, 07 August 2007 12:13 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I also meant to mention that I have reviewed message #49178 which may contain the answer I am looking for, but I did not understand why the new_time() function would produce and exception for daylight savings time. I suppose this means that from_tz() could produce a similar exception.
Re: Daylight Savings time [message #257162 is a reply to message #257158] Tue, 07 August 2007 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I get the following result on a 10.2.0.3 system (including time zone version 3):
11/03/2007 00:00:00.000 US/CENTRAL
11/03/2007 01:00:00.000 US/CENTRAL
11/03/2007 03:00:00.000 US/CENTRAL
11/03/2007 04:00:00.000 US/CENTRAL

01/04/2007 01:00:00.000 US/CENTRAL
01/04/2007 02:00:00.000 US/CENTRAL
01/04/2007 03:00:00.000 US/CENTRAL
01/04/2007 04:00:00.000 US/CENTRAL

28/10/2007 00:00:00.000 US/CENTRAL
28/10/2007 01:00:00.000 US/CENTRAL
28/10/2007 02:00:00.000 US/CENTRAL
28/10/2007 03:00:00.000 US/CENTRAL

04/11/2007 00:00:00.000 US/CENTRAL
04/11/2007 01:00:00.000 US/CENTRAL
04/11/2007 01:00:00.000 US/CENTRAL
04/11/2007 02:00:00.000 US/CENTRAL

You can use the attached script I wrote 6 months ago, it gives your level concerning DST.
For instance, on the same system:
SQL> @check_tz

Oracle version.............. 10.2.0.3.0
Core (DB) time zone version. 3
OJVM time zone version...... 3

Regards
Michel

  • Attachment: check_tz.sql
    (Size: 7.50KB, Downloaded 434 times)
Re: Daylight Savings time [message #257184 is a reply to message #257158] Tue, 07 August 2007 13:06 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thank you for the code.
This next issue will reveal my 'newbieness'.
I get the following error

Quote:
ORA-00922: missing or invalid option


on some of the set statements:

Set termout off
Set serveroutput on format wrap
...
Set termout off
Set feedback on


I put ';' at the end of all of the set statements and the received the error on all of the set statements.

Set termout off;
Set termout on;
Set serveroutput on format wrap;
Set feedback off;
Set define off;
...
Set termout off;
Set termout on;
Set feedback on;
Set define on;


Re: Daylight Savings time [message #257196 is a reply to message #257184] Tue, 07 August 2007 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should say it is a script for SQL*Plus.

Regards
Michel
icon5.gif  Re: Daylight Savings time [message #257203 is a reply to message #257158] Tue, 07 August 2007 14:04 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thank you again for the code. It ran like a champ.

I ran the code in SQL*Plus and it displayed:

Quote:
Oracle version.............. 10.2.0.1.0
Core (DB) time zone version. 4
OJVM time zone version...... >= 4


Does this tell me if the Oracle patch was or was not applied?

If the patch was not applied, is there a patch identification # to tell the DBA?

Thanks.
Re: Daylight Savings time [message #257204 is a reply to message #257203] Tue, 07 August 2007 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The patch was applied in the database.
Did you get the first result (your queries) from local or remote client?
And with which tool?

Regards
Michel

[Updated on: Tue, 07 August 2007 14:12]

Report message to a moderator

Re: Daylight Savings time [message #257205 is a reply to message #257158] Tue, 07 August 2007 14:21 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I have the Oracle client software on my laptop.

I am using the Embarcadero DBArtisan 8.1.4 tool to connect to Oracle and run the queries. I am not sure if that means I am running the queries from a local or remote client.

I also received the same results when running the queries in a SQL*Plus session started from DBArtisan.
Re: Daylight Savings time [message #257207 is a reply to message #257205] Tue, 07 August 2007 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I was thinking is that the database was patched but not the client.
There is a note on Metalink on the subject but I don't remember which one. It gives queries to check if the both side are at the same level regarding DST.

Regards
Michel
Re: Daylight Savings time [message #257209 is a reply to message #257207] Tue, 07 August 2007 14:45 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I found it.
It is note 402742.1 section 200.

Regards
Michel
Previous Topic: Oracle Time Difference
Next Topic: utl_file help
Goto Forum:
  


Current Time: Tue Dec 06 16:16:00 CST 2016

Total time taken to generate the page: 0.10203 seconds