Solved

Pulling DST for a future date from Oracle?

Posted on 2011-03-07
11
1,044 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
ID: 35059569
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
ID: 35059749
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
ID: 35059909
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
ID: 35059937
yes...this will only be dates after 2007....actually only future dates...
0
 
LVL 12

Author Comment

by:rgn2121
ID: 35059989
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 73

Expert Comment

by:sdstuber
ID: 35060087
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
ID: 35060114
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
ID: 35060132
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
ID: 35060134
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
ID: 35060161
I am closing shop for the day, but i will check this first thing tomorrow morning...Thanks!
0
 
LVL 12

Author Comment

by:rgn2121
ID: 35069370
Looks good...thanks for the help!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 58
Consolidating oracle query results to a single line 8 52
VBA MS Word "Table of Contents" extractor` 4 24
SQL Developer 6 32
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

920 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

17 Experts available now in Live!

Get 1:1 Help Now