Home » SQL & PL/SQL » SQL & PL/SQL » Wrong result in trying to find offset change time (Oracle 11.2.0.3.0 on OEL 5.7 to Oracle 11.2.0.1.0 OEL 5.7)
| Wrong result in trying to find offset change time [message #609254] |
Tue, 04 March 2014 15:50  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I am trying to write a code that will
*receives time zone name
*checks if there is an offset change in the next 300 hours
*if no => will print/return the UTC date as it will be in 300 hours from now
*if yes => will print/return the LOCAL date as it will be a second before the change kicks in
In one of my versions I run the following code:
SQL> declare
2 p_tzname varchar2(50) := 'America/Argentina/Cordoba';
3 p_hours number := 300;
4 --
5 v_offset_doesnt_change number(1) := 1;
6 v_halfhours_offset_check number(4, 1) := 0.5;
7 --
8 v_local_time date;
9 utc_time date;
10 v_curr_offset number(10, 5);
11 v_offset_in_300_hrs number(10, 5);
12 result varchar2(100);
13 begin
14 --initialize utc time:
15 select cast(sys_extract_utc(systimestamp) as date)
16 into utc_time
17 from dual;
18 dbms_output.put_line(utc_time);
19 --initialize offset from UTC at that timezone:
20 execute immediate 'select convert_alpha_time_to_offset(to_char(from_tz(cast(' ||
21 'to_date(''' || utc_time || ''')' || ' as timestamp),' || '''' ||
22 p_tzname || '''' || '),''tzh:tzm'')) from dual'
23 into v_curr_offset;
24 --initialize offset from UTC at that timezone in x hours from now:
25 execute immediate 'select convert_alpha_time_to_offset(to_char(from_tz(cast(' ||
26 'to_date(''' || utc_time || ''')+' || p_hours / 24 ||
27 ' as timestamp),' || '''' || p_tzname || '''' ||
28 '),''tzh:tzm'')) from dual'
29 into v_offset_in_300_hrs;
30 --initialize localtime:
31 v_local_time := utc_time + v_curr_offset;
32 --
33 --output the vars, so far:
34 dbms_output.put_line('utc_time is: ' || utc_time);
35 dbms_output.put_line('v_curr_offset is: ' || v_curr_offset || ' (' ||
36 round(v_curr_offset * 24) || ' hours)');
37 dbms_output.put_line('v_offset_in_300_hrs is: ' || v_offset_in_300_hrs || ' (' ||
38 round(v_offset_in_300_hrs * 24) || ' hours)');
39 dbms_output.put_line('v_local_time is: ' || v_local_time);
40 --
41 --check if current offset, and offset in "p_hours" from now will be the same:
42 if v_curr_offset = v_offset_in_300_hrs then
43 --if offset doesn't change - return the UTC time in 300 hours from now, trunced by bits of 30 minutes:
44 --return
45 dbms_output.put_line(utc_time + p_hours / 24 -
46 (mod(to_char(utc_time, 'mi'), 30) / 1440 +
47 mod(to_char(utc_time, 'ss'), 60) / (1440 * 60)));
48 else
49 --else , will check what will be the next time when time will change:
50 begin
51 --i1
52 while v_offset_doesnt_change = 1 loop
53 --check, by 0.5 hour increments, when is the offset changing:
54 execute immediate 'select count(*) from dual where
55 convert_alpha_time_to_offset(to_char(from_tz(cast(' ||
56 'to_date(''' || utc_time || ''')+' ||
57 v_halfhours_offset_check || '/' || 24 ||
58 ' as timestamp),' || '''' || p_tzname || '''' ||
59 '),''tzh:tzm'')) =
60 (select convert_alpha_time_to_offset(to_char(from_tz(cast(' ||
61 'to_date(''' || utc_time || ''')' ||
62 ' as timestamp),' || '''' || p_tzname || '''' ||
63 '),''tzh:tzm'')) from dual)'
64 into v_offset_doesnt_change;
65
66 v_halfhours_offset_check := v_halfhours_offset_check + 0.5;
67 --
68 -- dbms_output.put_line('v_offset_doesnt_change is: '||v_offset_doesnt_change);
69 -- v_offset_doesnt_change := 0;
70 end loop;
71 result := v_local_time + (v_halfhours_offset_check - 0.5) / 24 -
72 (mod(to_char(v_local_time, 'mi'), 30) / 1440 +
73 mod(to_char(v_local_time, 'ss'), 60) / (1440 * 60)) -
74 0.00001;
75 dbms_output.put_line('Local time , a second before offset changes is: '||result);
76 end; --i1
77 end if;
78 end;
79 /
04.03.2014 21:37:58
utc_time is: 04.03.2014 21:37:58
v_curr_offset is: -.08333 (-2 hours)
v_offset_in_300_hrs is: -.125 (-3 hours)
v_local_time is: 04.03.2014 19:37:58
Local time , a second before offset changes is: 15.03.2014 20:59:59
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.43
Unfortunately,
This does not fit to the results of the query I use to check myself about "predicting" offset changes:
SQL> set pages 200 lines 200
SQL> col offset_BEFORE_shift for a30
SQL> col offset_AFTER_estimated_shift for a30
SQL> col offset_AFTER_REAL_shift for a30
SQL> select to_char(from_tz(cast(to_date('2013-03-15 20:59:59',
2 'YYYY-MM-DD HH24:MI:SS') as timestamp),
3 'UTC') AT TIME ZONE 'America/Argentina/Cordoba',
4 'tzh:tzm') "offset_BEFORE_shift",
5 to_char(from_tz(cast(to_date('2013-03-15 22:59:59',
6 'YYYY-MM-DD HH24:MI:SS') as timestamp),
7 'UTC') AT TIME ZONE 'America/Argentina/Cordoba',
8 'tzh:tzm') "offset_AFTER_estimated_shift",
9 to_char(from_tz(cast(to_date('2013-03-17 02:00:00',
10 'YYYY-MM-DD HH24:MI:SS') as timestamp),
11 'UTC') AT TIME ZONE 'America/Argentina/Cordoba',
12 'tzh:tzm') "offset_AFTER_REAL_shift"
13 from dual;
offset_BEFORE_shift offset_AFTER_estimated_shift offset_AFTER_REAL_shift
------------------------------ ------------------------------ ------------------------------
-02:00 -02:00 -03:00
Elapsed: 00:00:00.00
I am clearly doing something wrong somewhere.. for most of the time zones in v$timezone_names I am getting it right,
But with some 5% I have this inaccuracy, and can't understand why 
Thanks in advance.
Regards,
Andrey
|
|
|
|
|
|
| Re: Wrong result in trying to find offset change time [message #609274 is a reply to message #609256] |
Wed, 05 March 2014 01:17  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
msol25 wrote on Wed, 05 March 2014 04:23hi,
Please can u tell me that what convert_alpha_time_to_offset at this place?
Sure, it's a function that converts an offset that you get by Oracle functions (from_tz, to_char) in format 'tzh:tzm', to a numerical value that you can use for math:
Here's the code:
CREATE OR REPLACE FUNCTION "CONVERT_ALPHA_TIME_TO_OFFSET"
(pTheTime varchar2)
return number
deterministic
parallel_enable
is
convertedTime number;
theTime varchar2(8);
multiplier number default 1;
begin
if substr(trim(pTheTime),1,1) in ('+','-') then
theTime := substr(trim(replace(pTheTime,':',''))||'000000',2,6);
if substr(trim(pTheTime),1,1)='-' then
multiplier := -1;
end if;
else
theTime := substr(trim(replace(pTheTime,':',''))||'000000',1,6);
end if;
begin
convertedTime := (to_date(lpad(trim(least(235959,theTime)),6,'0'),'HH24MISS') - round(to_date('01','HH')));
exception
when others then
dbms_output.put_line('Invalid Time:'||pTheTime);
end;
return (convertedTime*multiplier);
end CONVERT_ALPHA_TIME_TO_OFFSET;
[Updated on: Wed, 05 March 2014 01:18] Report message to a moderator |
|
|
|
|
|
| Re: Wrong result in trying to find offset change time [message #609297 is a reply to message #609276] |
Wed, 05 March 2014 04:01 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I have found my mistake, and very unfortunately, it is mostly semantic:
1.
When I checked my findings with the SQL:
SQL> set pages 200 lines 200
SQL> col offset_BEFORE_shift for a30
SQL> col offset_AFTER_estimated_shift for a30
SQL> col offset_AFTER_REAL_shift for a30
SQL> select to_char(from_tz(cast(to_date('2013-03-15 20:59:59',
2 'YYYY-MM-DD HH24:MI:SS') as timestamp),
3 'UTC') AT TIME ZONE 'America/Argentina/Cordoba',
4 'tzh:tzm') "offset_BEFORE_shift",
5 to_char(from_tz(cast(to_date('2013-03-15 22:59:59',
6 'YYYY-MM-DD HH24:MI:SS') as timestamp),
7 'UTC') AT TIME ZONE 'America/Argentina/Cordoba',
8 'tzh:tzm') "offset_AFTER_estimated_shift",
9 to_char(from_tz(cast(to_date('2013-03-17 02:00:00',
10 'YYYY-MM-DD HH24:MI:SS') as timestamp),
11 'UTC') AT TIME ZONE 'America/Argentina/Cordoba',
12 'tzh:tzm') "offset_AFTER_REAL_shift"
13 from dual;
offset_BEFORE_shift offset_AFTER_estimated_shift offset_AFTER_REAL_shift
------------------------------ ------------------------------ ------------------------------
-02:00 -02:00 -03:00
Elapsed: 00:00:00.00
I used the year 2013, whereas in my PL/SQL I was checking my next offset change, and we are in the year 2014 now...
(please ignore the fact that Goodle says Argentina/Cordoba has no offset change this year, my timezone file is old and "doesn't know" it).
2. In my PL/SQL code , I wasn't addressing the UTC timezone and it's difference from the specified timezone with the AT TIME ZONE clause.
I am doing it in my SQL so that's a good reason for building the syntax in a similar way
3. When I am checking with my SQL - I need to put the UTC dates and times in the quoted strings, and not the local ones. So from the result of the PL/SQL block I need to subtract the offset and put the result into the quoted string.
Thank you for your attempt and will to assist!
Best Regards,
Andrey R.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 14:10:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
