Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

timezone handling in oracle reports

Posted on 2005-03-03
14
Medium Priority
?
1,981 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
  • 7
  • 3
11 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

580 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