| tz_offset shows different value for different database [message #568911] |
Wed, 17 October 2012 09:11  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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 #568930 is a reply to message #568916] |
Wed, 17 October 2012 12:02  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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 #568965 is a reply to message #568933] |
Thu, 18 October 2012 04:00 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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 #569008 is a reply to message #569003] |
Thu, 18 October 2012 12:13 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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 
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 #569126 is a reply to message #569046] |
Sat, 20 October 2012 20:04 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
