• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1069
  • Last Modified:

Pulling DST for a future date from Oracle?

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
rgn2121
Asked:
rgn2121
  • 7
  • 4
2 Solutions
 
sdstuberCommented:
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
 
rgn2121Author Commented:
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
 
sdstuberCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rgn2121Author Commented:
yes...this will only be dates after 2007....actually only future dates...
0
 
rgn2121Author Commented:
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
 
sdstuberCommented:
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
 
rgn2121Author Commented:
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
 
rgn2121Author Commented:
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
 
sdstuberCommented:
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
 
rgn2121Author Commented:
I am closing shop for the day, but i will check this first thing tomorrow morning...Thanks!
0
 
rgn2121Author Commented:
Looks good...thanks for the help!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now