Solved

Pulling DST for a future date from Oracle?

Posted on 2011-03-07
11
1,039 Views
Last Modified: 2012-05-11
I am pulling data from a table that contains scheduling information and I am curious if there is a way for Oracle to calculate DST based on future dates.  Say I am pulling data from the table for 3/17/2011 17:00:00 GMT, that time local for me should be either 16:00:00 or 4PM instead of 5PM.

Now, I know that oracle can give me the correct time for my timezone based off of what the current date is, but is it possible to do this for a future date?  
I tried using the New_Time() function, that it uses (-5) because for the current sysdate DST is not in effect, but on 3/17/2011 it should be using -4...
0
Comment
Question by:rgn2121
  • 7
  • 4
11 Comments
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
Oracle won't do the DST conversion for you.

New_Time forces you to specify the ST or DT of each timezone

I wrote a modified version of new_time  called "sds_newtime"  that will support US "prevailing" timezones

usage is mostly the same as new_time

to convert from GMT to Eastern Prevailing time use...

sds_newtime(sysdate,'GMT','EPT')

CREATE OR REPLACE FUNCTION sds_newtime(p_date            IN DATE,
                                       p_from_timezone   IN VARCHAR2,
                                       p_to_timezone     IN VARCHAR2,
                                       p_2am             IN NUMBER DEFAULT 1
                                      )
    RETURN DATE
    DETERMINISTIC
IS
    v_year                 PLS_INTEGER;
    v_start_of_dst         DATE;
    v_end_of_dst           DATE;
    v_temp_date            DATE;
    v_to_daylight_offset   NUMBER;
    v_to_standard_offset   NUMBER;
BEGIN
    --
    -- If no conversions are between "Prevailing Time" timezones
    -- or if any of the inputs are NULL then act as standard NEW_TIME
    --
    IF    p_date IS NULL
       OR p_from_timezone IS NULL
       OR p_to_timezone IS NULL
       OR (    p_from_timezone NOT IN ('EPT', 'CPT', 'MPT', 'PPT', 'YPT', 'HPT')
           AND p_to_timezone NOT IN ('EPT', 'CPT', 'MPT', 'PPT', 'YPT', 'HPT'))
    THEN
        RETURN NEW_TIME(p_date, p_from_timezone, p_to_timezone);
    END IF;

    --
    -- Determine the daylight/standard transition dates.
    --
    v_year  := TO_NUMBER(TO_CHAR(p_date, 'yyyy'));

    IF v_year >= 2007
    THEN
        -- Rule established: August 8, 2005 [ HR6] Bush
        --   Law name: 'Energy Policy Act of 2005'
        -- DST starts on Second Sunday in March starting 2007
        v_start_of_dst      :=
            NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-03-01', 'yyyy-mm-dd') - 1, 'SUNDAY') + 7;
        -- DST ends on First Sunday in November starting 2007
        v_end_of_dst  := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-10-31', 'yyyy-mm-dd'), 'SUNDAY');
    ELSIF v_year >= 1987
    THEN
        -- Rule established: October 8, 1986 [P.L. 99-359] Reagan
        --   Law name: 'Fire Prevention and Control Authorizations Act'
        -- DST starts on First Sunday in April starting in 1987
        v_start_of_dst  := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-03-31', 'yyyy-mm-dd'), 'SUNDAY');
        -- DST ends on Last Sunday in October
        v_end_of_dst    := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-10-24', 'yyyy-mm-dd'), 'SUNDAY');
    ELSIF v_year = 1975
    THEN
        -- Rule established: December 15, 1973 [P.L. 93-182] Nixon
        --   Law name: 'Emergency Daylight Saving Time Energy Conservation Act of 1973'
        --   amended by [P.L. 93-434]
        v_start_of_dst  := TO_DATE('1975-02-23', 'yyyy-mm-dd');
        -- DST ends Last Sundary in October
        v_end_of_dst    := TO_DATE('1975-10-26', 'yyyy-mm-dd');
    ELSIF v_year = 1974
    THEN
        -- Rule established: December 15, 1973 [P.L. 93-182] Nixon
        --   Law name: 'Emergency Daylight Saving Time Energy Conservation Act of 1973'
        v_start_of_dst  := TO_DATE('1974-01-06', 'yyyy-mm-dd');
        v_end_of_dst    := TO_DATE('1974-10-27', 'yyyy-mm-dd');
    ELSIF p_date >= TO_DATE('1966-04-24', 'yyyy-mm-dd')
    THEN
        -- Rule established: April 13, 1966 [Public Law 89-387] Johnson
        --   Law name: 'The Uniform Time Act of 1966' (15 U.S. Code Section 260a)
        -- DST starts on Last Sunday in April
        v_start_of_dst  := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-04-23', 'yyyy-mm-dd'), 'SUNDAY');
        -- DST ends on Last Sunday in October
        v_end_of_dst    := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-10-24', 'yyyy-mm-dd'), 'SUNDAY');
    ELSE
        --
        -- For dates prior to the Uniform Time Act of 1966 we can't produce a standardized algorithm
        -- all "Prevailing" timezones are invalid for these dates, raise an error reporting them as such.
        --
        raise_application_error(
            -20000,
            'U.S. "prevailing" time not supported prior to The Uniform Time Act of 1966');
    END IF;

    -- convert all "prevailing" times to and from GMT
    -- Adjust for DST if necessary

    -- For switch back to Standard time
    -- there are two periods of 1:00:00am to 1:59:59am on this day.
    -- 1:59:59am Daylight Saving "falls back" to 1:00:00am Standard

    -- At the instant the first 2am of the Fall switch date is reached
    -- Daylight Saving Time ends and it becomes 1am Standard instead
    -- For simplicity of calculation,

    -- Check if we're in Standard time
    -- Before Spring transition, or after Fall transition
    IF p_from_timezone IN ('EPT', 'CPT', 'MPT', 'PPT', 'YPT', 'HPT')
    THEN
        IF    p_date <= (v_start_of_dst + 2 / 24)
           OR p_date >= (v_end_of_dst + 2 / 24)
           OR (p_date >= (v_end_of_dst + 1 / 24) AND p_2am != 1)
        THEN
            -- We're in Standard Time
            IF p_from_timezone = 'EPT'
            THEN
                v_temp_date  := p_date + 5 / 24;
            ELSIF p_from_timezone = 'CPT'
            THEN
                v_temp_date  := p_date + 6 / 24;
            ELSIF p_from_timezone = 'MPT'
            THEN
                v_temp_date  := p_date + 7 / 24;
            ELSIF p_from_timezone = 'PPT'
            THEN
                v_temp_date  := p_date + 8 / 24;
            ELSIF p_from_timezone = 'YPT'
            THEN
                v_temp_date  := p_date + 9 / 24;
            ELSIF p_from_timezone = 'HPT'
            THEN
                v_temp_date  := p_date + 10 / 24;
            END IF;
        ELSE
            -- We're in Daylight Saving Time
            IF p_from_timezone = 'EPT'
            THEN
                v_temp_date  := p_date + 4 / 24;
            ELSIF p_from_timezone = 'CPT'
            THEN
                v_temp_date  := p_date + 5 / 24;
            ELSIF p_from_timezone = 'MPT'
            THEN
                v_temp_date  := p_date + 6 / 24;
            ELSIF p_from_timezone = 'PPT'
            THEN
                v_temp_date  := p_date + 7 / 24;
            ELSIF p_from_timezone = 'YPT'
            THEN
                v_temp_date  := p_date + 8 / 24;
            ELSIF p_from_timezone = 'HPT'
            THEN
                v_temp_date  := p_date + 9 / 24;
            END IF;
        END IF;
    ELSE
        v_temp_date  := NEW_TIME(p_date, p_from_timezone, 'GMT');
    END IF;
   
    IF p_to_timezone IN ('EPT', 'CPT', 'MPT', 'PPT', 'YPT', 'HPT')
    THEN
        IF p_to_timezone = 'EPT'
        THEN
            v_to_standard_offset  := -5 / 24;
            v_to_daylight_offset  := -4 / 24;
        ELSIF p_to_timezone = 'CPT'
        THEN
            v_to_standard_offset  := -6 / 24;
            v_to_daylight_offset  := -5 / 24;
        ELSIF p_to_timezone = 'MPT'
        THEN
            v_to_standard_offset  := -7 / 24;
            v_to_daylight_offset  := -6 / 24;
        ELSIF p_to_timezone = 'PPT'
        THEN
            v_to_standard_offset  := -8 / 24;
            v_to_daylight_offset  := -7 / 24;
        ELSIF p_to_timezone = 'YPT'
        THEN
            v_to_standard_offset  := -9 / 24;
            v_to_daylight_offset  := -8 / 24;
        ELSIF p_to_timezone = 'HPT'
        THEN
            v_to_standard_offset  := -10 / 24;
            v_to_daylight_offset  := -9 / 24;
        END IF;

        IF v_temp_date + v_to_standard_offset < (v_start_of_dst + 2 / 24)
        THEN
            -- Standard time is before the first transition
            v_temp_date  := v_temp_date + v_to_standard_offset;
        ELSIF v_temp_date + v_to_standard_offset < (v_end_of_dst + 1 / 24)
        THEN
            -- Between the Spring and Fall switch times is the Daylight Saving period
            v_temp_date  := v_temp_date + v_to_daylight_offset;
        ELSE
            -- p_date is on or after 2am on Fall switch date so it's Standard
            -- All times are standard so no further change is needed.
            v_temp_date  := v_temp_date + v_to_standard_offset;
        END IF;
    ELSE
        v_temp_date  := NEW_TIME(v_temp_date, 'GMT', p_to_timezone);
    END IF;

    RETURN v_temp_date;
END sds_newtime;
/

Open in new window

0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
Thanks...I don't have rights to create functions, but I am talking with the DBA now to see what can be done.  I might just have to continue doing what I am doing now, but I appreciate the help....I will let you know!
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you only need to convert GMT to Eastern and only for times after 2007 (when the last DST changes went into effect)  then you could simply embed the rules directly into your queries

CASE
           WHEN your_gmt_date <
                      NEXT_DAY(ADD_MONTHS(TRUNC(your_gmt_date, 'yyyy'), 2) - 1, 'SUNDAY')
                    + 7
                    + 2 / 24
           THEN
               your_gmt_date - 5 / 24
           WHEN your_gmt_date <
                    NEXT_DAY(ADD_MONTHS(TRUNC(your_gmt_date, 'yyyy'), 10) - 1, 'SUNDAY') + 1 / 24
           THEN
               your_gmt_date - 4 / 24
           ELSE
               your_gmt_date - 5 / 24
       END

Open in new window

0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
yes...this will only be dates after 2007....actually only future dates...
0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
That looks to do what I needed...so if my time is 03/13/2011 01:45:00 it will be -5 and 03/13/2011 02:45:00 it will be -4?

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
yep, just test to confirm...
SELECT your_gmt_date,
       CASE
           WHEN your_gmt_date <
                      NEXT_DAY(ADD_MONTHS(TRUNC(your_gmt_date, 'yyyy'), 2) - 1, 'SUNDAY')
                    + 7
                    + 2 / 24
           THEN
               your_gmt_date - 5 / 24
           WHEN your_gmt_date <
                    NEXT_DAY(ADD_MONTHS(TRUNC(your_gmt_date, 'yyyy'), 10) - 1, 'SUNDAY') + 1 / 24
           THEN
               your_gmt_date - 4 / 24
           ELSE
               your_gmt_date - 5 / 24
       END
           ept_date
  FROM (SELECT TO_DATE('03/13/2011 01:45:00', 'mm/dd/yyyy hh24:mi:ss') your_gmt_date FROM DUAL
        UNION ALL
        SELECT TO_DATE('03/13/2011 02:45:00', 'mm/dd/yyyy hh24:mi:ss') FROM DUAL)

Open in new window

0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
The date field I have coming in is GMT...So do I need to convert it first?
I have a time on 3/13/2011 02:20:00 AM GMT...If I am correct, the time change for me (EST) doesn't go into effect until 07:00:00 GMT...correct?
0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
3/13/2011 06:45:00 is 01:45:00 local time for me....so wouldn't it be anything that is past 07 in the spring and 06 in the fall?  I might be getting things confused....
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
you're right...

I had the adjustment correct but was comparing to EST dates, not GMT dates  
SELECT your_gmt_date,
       CASE
           WHEN your_gmt_date <
                      NEXT_DAY(ADD_MONTHS(TRUNC(your_gmt_date, 'yyyy'), 2) - 1, 'SUNDAY')
                    + 7
                    + 7 / 24
           THEN
               your_gmt_date - 5 / 24
           WHEN your_gmt_date <
                    NEXT_DAY(ADD_MONTHS(TRUNC(your_gmt_date, 'yyyy'), 10) - 1, 'SUNDAY') + 6 / 24
           THEN
               your_gmt_date - 4 / 24
           ELSE
               your_gmt_date - 5 / 24
       END
           ept_date
  FROM (SELECT TO_DATE('03/13/2011 01:45:00', 'mm/dd/yyyy hh24:mi:ss') your_gmt_date FROM DUAL
        UNION ALL
        SELECT TO_DATE('03/13/2011 02:45:00', 'mm/dd/yyyy hh24:mi:ss') FROM DUAL)

Open in new window

0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
I am closing shop for the day, but i will check this first thing tomorrow morning...Thanks!
0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
Looks good...thanks for the help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now