?
Solved

timezone handling in oracle reports

Posted on 2005-03-03
14
Medium Priority
?
1,975 Views
Last Modified: 2008-01-09
HI,

We deal with timezone in our databases.  if it is EPT between Ist sunday of Apr to last sunday of Oct , then it is EST(current time) date +1/24 or else  est is same as as ept.

EPT EASTERN PREVAILING TIME
EST EASTERN STANDARD TIME

How can i use the function given below to be incorporated in the reports , that when the user pulls the est / ept from drop down list, it automatically gives the data for the corresponding timezone with the correct dates.


any help

Right now we have table hr_info which has time, time_ept as columns and is filled by a procedure with respective date and times and all our reports are joined to this table which is not very efficient like the the table is getting overfilled, or if something goes wrong in the table all the reports will not work.

sample report select:

select decode(:timezone, 'EPT', h.time_ept, h.time), u.unit from unit u, hr_info h
where u.date_time = h.time;


We have also have this function in our database.

function date_timezone_adjusted(input_date date, to_timezone varchar2 := 'EPT',other varchar2 := 'EST')
  return date is
  from_timezone  constant varchar2(3) := 'EST';
  date_adjusted  date;
begin
  date_adjusted := input_date;
  if upper(to_timezone) = 'EPT' then
      if input_date between date_start_dst(to_char(input_date,'RRRR'))
                        and date_end_dst(to_char(input_date,'RRRR')) then
      date_adjusted := new_time(input_date,from_timezone,'EDT');
      end if;
  elsif upper(to_timezone) = 'CST' then
    date_adjusted := new_time(input_date,from_timezone,upper(to_timezone));
  elsif upper(to_timezone) = 'CPT' then
    date_adjusted := new_time(input_date,from_timezone,'CST');
    if input_date between date_start_dst(to_char(input_date,'RRRR'))
                     and date_end_dst(to_char(input_date,'RRRR')) then
     date_adjusted := new_time(input_date,from_timezone,'CDT');
    end if;
  end if;
  if upper(other) = 'EPT' then
     if input_date between date_start_dst(to_char(input_date,'RRRR'))
                        and date_end_dst(to_char(input_date,'RRRR')) then
      date_adjusted := new_time(input_date,from_timezone,'CST');
       end if;
  end if;
    return date_adjusted;
end date_timezone_adjusted;



any help would be app
0
Comment
Question by:SNEHA04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
14 Comments
 
LVL 9

Expert Comment

by:neo9414
ID: 13452295
what is the function date_start_dst doing???
0
 

Author Comment

by:SNEHA04
ID: 13452353
I think it is looking for the first sunday in april for every year and the other function is looking for the last sunday in octorber for every year

function date_start_dst(input_year varchar2)
  return date is
  v_date   date;
begin
  if length(input_year) = 4 then
      for i in 1..7 loop
        v_date := to_date(input_year||'04'||i||' 02','RRRRMMDD HH24');
          if to_char(v_date, 'D') = '1' then
            exit;
          end if;
      end loop;
  end if;
  return v_date;

end;

function date_end_dst(input_year varchar2)
  return date is
  v_date   date;
begin
   if length(input_year) = 4 then
      for i in 25..31 loop
        v_date := to_date(input_year||'10'||i||' 02','RRRRMMDD HH24');
          if to_char(v_date, 'D') = '1' then
            exit;
          end if;
      end loop;
   end if;
   return v_date;
end;
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13452484
anyways...I have managed without it

here is the function (I have just written it for 'EPT', please add the others)


create or replace function date_timezone_adjusted(input_date date, to_timezone varchar2 := 'EPT',other varchar2 := 'EST')
  return date is
  from_timezone  constant varchar2(3) := 'EST';
  date_adjusted  date;
  startEPT Date;
  endEPT   Date;
  input_year varchar2(4);
begin
  date_adjusted := input_date;
 
  select to_char(input_date,'RRRR')
  into input_year
  from dual;

  select next_day(to_date('01-apr-'||input_year,'dd-mon-rrrr'),'Sunday'), next_day(to_date('31-oct-'||input_year,'dd-mon-rrrr')-7,'Sunday')
  INTO startEPT,endEPT
  from dual;  
 
  if upper(to_timezone) = 'EPT' then
     if (to_number(to_char(input_date,'mm')) between 4 and 10)
       and trunc(input_date) >= startEPT
       and trunc(input_date) <= endEPT then
      date_adjusted := new_time(input_date,from_timezone,'EDT');
     end if;
  end if;    
    return date_adjusted;
end date_timezone_adjusted;
/

testcase:

sql> select to_char(date_timezone_adjusted(to_date('10-oct-2005'),'EPT',null),'dd-mon-yyyy hh24:mi:ss') from dual

--------------------
TO_CHAR(DATE_TIMEZON
--------------------
10-oct-2005 01:00:00

sql>select to_char(date_timezone_adjusted(to_date('10-mar-2005'),'EPT',null),'dd-mon-yyyy hh24:mi:ss') from dual

TO_CHAR(DATE_TIMEZON
--------------------
10-mar-2005 00:00:00

note: You will see that in the first test the time has exceeded by 1 hour (EDT).

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:neo9414
ID: 13452501
let me know if this is what you want.

Also if you want me to add the remaining conditions in the function then I will do it for you. but before that test the above function.

0
 

Author Comment

by:SNEHA04
ID: 13452713
But when I use this in a data extraction in report it takes a long time.  How do I use the same function what I have in a different way.

thank you,
0
 

Author Comment

by:SNEHA04
ID: 13452714
select decode(:timezone, 'EPT', h.time_ept, h.time), u.unit from unit u, hr_info h
where u.date_time = h.time;
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13452821
The sql in the above post doesn't has any meaning for me..totally lost

I am not sure what you mean by " How do I use the same function what I have in a different way."
MY function is very similar to your function except it does not uses date_start_dst,date_end_dst functions as in your function. I think my function should definitely work faster than your current function.

Is the speed delay is because of this function or some other stuff...

can you give some more explaination....
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13452832
i will try to build my above function in a single sql query..
0
 
LVL 1

Expert Comment

by:lookingforjava
ID: 13459611
Have you tried to create a function-based index?  You have define your functions as deterministic such as

function date_start_dst(input_year varchar2)
  return date deterministic is

User defined functions can be indexed if the maximum size is less than 40 bytes.  So return values of number and date work for creating function based indexes.

To create the index use

create index hr_info_ept_idx on hr_info ( date_timezone_adjusted(time, 'EPT','EST') );
create index hr_info_est_idx on hr_info( date_timezone_adjusted(time,'EST','EST'));

You can create indexes for any other timezones as well.

This should reduce the gets on your database.

Your new report query can look like:

select (date_timezone_adjusted(h.time,:timezone,'EST')) adj_time, u.unit from unit u, hr_info h
where u.date_time = h.time;

You may have to set some pragmas to allow for the function to be selected in your report.

Refer to the PL/SQL reference manual at http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/toc.htm


0
 
LVL 9

Expert Comment

by:neo9414
ID: 13459889
in the above case the function based index  on "date_timezone_adjusted" won't help at all.

sneha:
select (date_timezone_adjusted(h.time,:timezone,'EST')) adj_time, u.unit from unit u, hr_info h
where u.date_time = h.time;

1. do u have index on unit(date_time). If not then create it.
2. its not required to create index on  hr_info(time) but you can create on if step 1 doesn't helps.

execute the query..if it is still slow then send me the execution plan for the above query

you may need to use some hints. but i will tell more on that only on seeing the execution plan. Just let me know..
0
 
LVL 9

Accepted Solution

by:
neo9414 earned 2000 total points
ID: 13464892
oops....
your query
select (date_timezone_adjusted(h.time,:timezone,'EST')) adj_time, u.unit from unit u, hr_info h
where u.date_time = h.time;

1. create index on hr_info(time)
2. no need to create on unit(date_time)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question