Home » SQL & PL/SQL » SQL & PL/SQL » tz_offset shows different value for different database (windows server 2008 R2 Oracle 11.1.0.7.0 Vs Linux Oracle 11.2.0.1.0)
tz_offset shows different value for different database [message #568911] Wed, 17 October 2012 09:11 Go to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,

I see a strange result when I check for the offset in different databases,
From the same SQL*Plus window.
(Probably something's configured "strangely" on one of my databases, but i can't seem to understand what).


SQL> 
SQL> conn sys/**********@db1 as sysdba
Connected.
SQL> 
SQL> col sysdate for a20
SQL> col currdate for a20
SQL> col stz for a10
SQL> col offset for a10
SQL> col dbtz for a10
SQL> 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi';

Session altered.

SQL> 
SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production                       
PL/SQL Release 11.1.0.7.0 - Production                                          
CORE	11.1.0.7.0	Production                                                      
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production                         
NLSRTL Version 11.1.0.7.0 - Production                                          

SQL> 
SQL> select sysdate,current_date as currdate,
  2  sessiontimezone as stz,
  3  dbtimezone as dbtz,
  4  tz_offset('Australia/Sydney') as offset
  5  from dual;

SYSDATE              CURRDATE             STZ        DBTZ       OFFSET          
-------------------- -------------------- ---------- ---------- ----------      
2012-10-17 15:09     2012-10-17 16:09     +02:00     +00:00     +10:00          

SQL> 
SQL> 
SQL> conn sys/**********@db2 as sysdba
Connected.
SQL> 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi';

Session altered.

SQL> 
SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production                       
PL/SQL Release 11.2.0.1.0 - Production                                          
CORE	11.2.0.1.0	Production                                                      
TNS for Linux: Version 11.2.0.1.0 - Production                                  
NLSRTL Version 11.2.0.1.0 - Production                                          

SQL> 
SQL> select sysdate,current_date as currdate,
  2  sessiontimezone as stz,
  3  dbtimezone as dbtz,
  4  tz_offset('Australia/Sydney') as offset
  5  from dual;

SYSDATE              CURRDATE             STZ        DBTZ       OFFSET          
-------------------- -------------------- ---------- ---------- ----------      
2012-10-17 16:05     2012-10-17 18:05     +02:00     +00:00     +11:00          

SQL> 
SQL> 
SQL> spool off



Does anybody know where this difference comes from?
why in db1 it shows +10:00, and on db2 it shows +11:00?

Thanks in advance,
Andrey
Re: tz_offset shows different value for different database [message #568916 is a reply to message #568911] Wed, 17 October 2012 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Time zone files are not of the same date.

Regards
Michel
Re: tz_offset shows different value for different database [message #568930 is a reply to message #568916] Wed, 17 October 2012 12:02 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Thank you Michel. This makes it all (partially) clearer.

I am trying to understand what will be best to advise for a customer.
There is so much information in MOS knowledge-base that I can't seem to understand what is right:

* applying to the latest time zone file version(how do I know what is it)
* or just stick to the minimum(7)

Regards,
Andrey
Re: tz_offset shows different value for different database [message #568933 is a reply to message #568930] Wed, 17 October 2012 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first option is the best one, at least for the client, this is the only one that will give the correct results.

Regards
Michel
Re: tz_offset shows different value for different database [message #568965 is a reply to message #568933] Thu, 18 October 2012 04:00 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Thanks Michecl.

I'm still having confusions..

Oracle note -" Applying the DSTv18 update for the Oracle Database [ID 1448706.1]"
says:

"Compared to DSTv17 this is the list of updated timezones in DSTv18 for non historical dates ( meaning update to the timezone after 1970)"

and then goes a list, in which I see Fiji, which has a particular interest for me.


On the other hand, In note "Updated DST transitions and new Time Zones in Oracle Time Zone File patches [ID 412160.1]"

I see
Current non-historical changes to the start and end date of DST for some regions / country's that are not included in DSTv18 ( TZ / Olson tzdata2012c update):
status on 13 Sept 2012

tzdata2012f:
* australasia (Pacific/Fiji): Fiji DST is October 21 through January 20 this year.

So I don't understand - Is the V18 time zone file enough to be covered for the DST offsets before and right after the upcoming time shift on 21st October, or NOT?

Is there a place where I can ask Oracle Support this kind of questions and get a reasonable reply,
To clarify time zone file versions?

Thanks and Best Regards,
Andrey

[Updated on: Thu, 18 October 2012 04:25]

Report message to a moderator

Re: tz_offset shows different value for different database [message #568967 is a reply to message #568965] Thu, 18 October 2012 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you have to open a SR to get a clear answer (I hope).

Regards
Michel
Re: tz_offset shows different value for different database [message #569003 is a reply to message #568967] Thu, 18 October 2012 08:43 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

I will. Thank you.
Re: tz_offset shows different value for different database [message #569008 is a reply to message #569003] Thu, 18 October 2012 12:13 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi All,
I've applied Oracle Timezone file Version 18 (latest) using instructions in Oracle documentation
http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#CHDBDEBI

Eventually, "select * from v$timezone_file" gives me the correct file version(18),
SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_18.dat              18


And I have no errors in sys.dst$error_table and in sys.dst$trigger_table:

SQL> select * from sys.dst$error_table;

no rows selected

SQL> select * from sys.dst$trigger_table;

no rows selected


Yet, I still don't see Fiji's time shift offset change on 20th of October 2012 at 13:59 UTC time
(at 12:00 Offset shifts from +12 to +13 and time shifts in Pacific/Fiji from 02:00 to 03:00)

SQL> select to_char(from_tz(cast(to_date('2012-10-15 10:30',
  2                                      'YYYY-MM-DD HH24:MI') as timestamp),
  3                         'UTC') AT TIME ZONE 'Pacific/Fiji',
  4                 'tzh:tzm')
  5    from dual
  6  union all
  7  select to_char(from_tz(cast(to_date('2012-10-25 15:59',
  8                                      'YYYY-MM-DD HH24:MI') as timestamp),
  9                         'UTC') AT TIME ZONE 'Pacific/Fiji',
 10                 'tzh:tzm')
 11    from dual;

TO_CHA
------
+12:00
+12:00




Edited: I've re-done the whole procedure from start
(reverted the VM I'm using for test to a before-upgrade snapshot and done it all again with attention to detals)
Now no tables in progress in DBA_TSTZ_TABLES(just part of the list):
SQL> select * from DBA_TSTZ_TABLES;

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            OPTSTAT_HIST_CONTROL$          NO
SYS                            WRI$_OPTSTAT_IND_HISTORY       NO
SYS                            WRI$_OPTSTAT_OPR               NO
SYS                            SCHEDULER$_WINDOW              NO
WMSYS                          WM$VERSIONED_TABLES            NO
SYSMAN                         MGMT_PROV_IP_RANGE             NO
FRA_W1_460                     AQ$_SM_CHECK_QTABLE_L          NO


Still, The query shows that the offset between Pacific/Fiji to UTC stays +12:00 with the latest time zone file Version 18 Sad

Any advice?


p.s Dear Moderator, please let me know If I should open this as a separate topic and on which Forum. Thank you

Thanks in advance,
Andrey

[Updated on: Thu, 18 October 2012 13:31]

Report message to a moderator

Re: tz_offset shows different value for different database [message #569046 is a reply to message #569008] Fri, 19 October 2012 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
[This topic is well as it is and where it is, go on here.

Regards
Michel]
Re: tz_offset shows different value for different database [message #569126 is a reply to message #569046] Sat, 20 October 2012 20:04 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Well, as I've installed the V18 and Fiji's offset remained +12:00,
Plus, Metalink note regarding this patch not including Fiji's time shift,
And that this time shift will be updated in V19 which will be available only on 15 of December,
I've opened a SR in MOS.

I still think its kind of ridiculous that Oracle releases update for a change in Oct 21st, 2012 on December 2012.

If someone by any chance installed that V18 patch - please let me know if the following query
Returns the correct result (+13:00) or not(+12:00)

select to_char(from_tz(cast(to_date('2012-10-25 15:59',
                                      'YYYY-MM-DD HH24:MI') as timestamp),
                         'UTC') AT TIME ZONE 'Pacific/Fiji',
                 'tzh:tzm')
    from dual;


Anyway, I will share Oracle's answer with you,
Although, I do not expect much...

Regards,
Andrey
Re: tz_offset shows different value for different database [message #569128 is a reply to message #569126] Sun, 21 October 2012 02:06 Go to previous messageGo to next message
Andrey_R
Messages: 194
Registered: January 2012
Location: Euro-Asia
Senior Member

Ok got MOS reply.

They've quoted their document that says that an update for this time shift on October 21st in Fiji will exist only in Version 19 that is due to be released on December 15th of this year. I guess that's it.

I patched my application to retrieve Oracle's offset +1 until the customer downloads and installs this patch.

Regards,
Andrey
Re: tz_offset shows different value for different database [message #569132 is a reply to message #569128] Sun, 21 October 2012 02:57 Go to previous message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for all your feedbacks.

Regards
Michel
Previous Topic: Help for SQL
Next Topic: How to bypass putting select inside a count function in the following query ?
Goto Forum:
  


Current Time: Fri Dec 19 21:26:14 CST 2014

Total time taken to generate the page: 0.10268 seconds