Home » SQL & PL/SQL » SQL & PL/SQL » wrong time offset calculation (Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Linux)
wrong time offset calculation [message #567808] Fri, 05 October 2012 09:37 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,

I want to check an incident that occurs each time
when the clock shifts in Australia, in our application.
(maybe not just in that region,but that's where it is commonly noticed)

I've checked our SQL\PLSQL code used to retrieve timezone related entity values,
and also played with the Server clock to simulate the next clock shift.

In Australia, next time the clock changes is
on 7th of October,2012 at 01:59, Australian time(Sydney)
.

What happens is that instead of the clock moving to 02:00,
it skips directly to 03:00
, as daylight savings time rule.

in UTC terms, this will happen on 6th of October at 15:59. (UTC time).
So, UTC 15:59 = 01:59(Aus/Sydney time),
However, 16:00 UTC = 03:00(Aus/Sydney time).

basically, the offset is changing at that threshold from +10 to +11.

I have changed the time and date on the database server to some 6th of October, 2012 at 16:45,
which should be 03:45 , 7th of October in Australia, Sydney, and offset should be no longer +10, but +11.

I have tried some variations of the code sent to the database server by the application,
and it seems I'm getting something wrong as my offset calculated by this query,
still seems to be only +10, and not +11, as I would expect.

This is my code:

SQL>
SQL>  select
  2   to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')
  3    from dual;

TO_CHAR(SYSDATE,'DD
-------------------
06/10/2012 16:49:01

SQL> select to_char(to_timestamp_tz(to_char(to_date('06/10/2012' || ' ' ||
  2                                                 trim(to_char(to_number(to_char(sysdate,
  3                                                                                'HH24'),
  4                                                                        '99'),
  5                                                              '00')) || ':' ||
  6                                                 trim(to_char(to_number(to_char(sysdate,
  7                                                                                'MI'),
  8                                                                        '99'),
  9                                                              '00')),
 10                                                 'DD/MM/YYYY HH24:MI'),
 11                                         'DD/MM/YYYY HH24:MI') || ' ' ||
 12                                 'Australia/Sydney',
 13                                 'dd/mm/yyyy hh24:mi tzr'),
 14                 'tzh:tzm')
 15    from dual
 16  ;

TO_CHA
------
+10:00

SQL>


Why don't I get +11 as a result of this offset calculating query?


Thanks in advance,
Andrey

[Updated on: Fri, 05 October 2012 09:54]

Report message to a moderator

Re: wrong time offset calculation [message #567816 is a reply to message #567808] Fri, 05 October 2012 11:43 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Any ideas???
I'm searching and reading but i don't seem to find a clear answer..
Thanks,
Andrey
Re: wrong time offset calculation [message #567819 is a reply to message #567816] Fri, 05 October 2012 22:58 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As you said yourself, daylight savings here in Oz doesn't kick in until 2am on 7/10. The timestamp that you are assembling (very laboriously) is some time on 6/10. So the offset on 6/10 Sydney time is +10. The offset after 2pm on 7/10 will be +11.

These queries will reutn +10:00 and +11:00 respectively

select to_char(from_tz(cast(to_date('2012-10-07 01:30', 'YYYY-MM-DD HH24:MI') as timestamp), 'Australia/Sydney'), 'tzh:tzm')
from dual

select to_char(from_tz(cast(to_date('2012-10-07 03:30', 'YYYY-MM-DD HH24:MI') as timestamp), 'Australia/Sydney'), 'tzh:tzm')
from dual


UTC has nothing to do with these conversions. You seem to think that to_timezone_tz will perform a UTC to Aus/Syd timeshift. It won't. First you need to EXPLICITLY place a timestamp in UTC and then use AT TIME ZONE to perform a timeshift.

select to_char(
    from_tz(cast(to_date('2012-10-06 16:45', 'YYYY-MM-DD HH24:MI') as timestamp), 'UTC') AT TIME ZONE 'Australia/Sydney'
 , 'tzh:tzm')
from dual

+11:00


Ross Leishman

Re: wrong time offset calculation [message #567831 is a reply to message #567819] Sat, 06 October 2012 09:53 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Ross - first of all great thanks for answering this.

I've tried the next query and it still gives me offset of 10 instead of 11...
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select sysdate,
  2         to_char(to_timestamp_tz(to_char(sysdate, 'YYYY-MM-DD HH24:MI') ||
  3                                 'Australia/Sydney',
  4                                 'YYYY-MM-DD HH24:MI TZR') AT TIME ZONE
  5                 'Australia/Sydney',
  6                 'TZH:TZM')
  7    from dual
  8  ;

SYSDATE   TO_CHA
--------- ------
06-OCT-12 +10:00

what could I be doing wrong?

Thanks in advance,
Andrey
Re: wrong time offset calculation [message #567838 is a reply to message #567831] Sat, 06 October 2012 19:25 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are constructing a timestamp in the Aus/Syd timezone and then time-shifting it to Aus/Syd. So that is a timeshift of ZERO hours.

TO_TIMESTAMP_TZ does NOT do what you think it does. All it does is construct a TIMESTAMP WITH TIME ZONE from a string. The string you are using is

2012-10-06 HH:MI Australia/Sydney

On 6/10/2012, the timezone offset of Sydney is +10.

If you want to time-shift from UTC. First you need to construct a UTC timestamp. If you insist upon using TO_TIMESTAMP_TZ instead of FROM_TZ, then you will need to start with the string:

2012-10-06 HH:MI UTC



Ross Leishman
Re: wrong time offset calculation [message #568328 is a reply to message #567838] Wed, 10 October 2012 17:12 Go to previous message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Ross Leishman, Thank you very much for your answers, they were very helpful.
Previous Topic: SQL Performance Analyzer Recommendation
Next Topic: Connect role in dba_role_privs
Goto Forum:
  


Current Time: Wed Oct 22 09:22:20 CDT 2014

Total time taken to generate the page: 0.17206 seconds