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

need a pl/sql function to return if a DATE is in DAYLIGHT SAVINGS TIME.

I need a pl/sql function that takes in a date and returns a boolean as to weather the date is in daylight savings time.

It should take a date as input but if there is none it should default to current date and time(sysdate).

It needs to figure out if the input date (or default today) is in daylight savings time or not.  It starts on the 2nd Sunday in March at midnight and it ends on the 1st Sunday in November at midnight.  It should return a Boolean value to indicate true if it is in DST and false otherwise.
0
kfeiner
Asked:
kfeiner
  • 4
  • 3
  • 2
1 Solution
 
sventhanCommented:
This could be useful to you. But this function  needs one more parameter as offset, which needs to identify the timezone.  

Hope you'll get the idea.. or something to start with.

CREATE OR REPLACE PACKAGE "ALL_DATE_CONVERT"
IS
/*****************************************************************************
* NAME : CONVERT_GMT_TO_LOCAL
*
* PURPOSE           : This procedure will convert the GMT to local time
*`
******************************************************************************/
   FUNCTION convert_gmt_to_local(
      date_i               IN   DATE,
      offset_i             IN   NUMBER,
      daylight_savings_i   IN   NUMBER
   ) RETURN DATE ;
     



/*****************************************************************************
* NAME : GET_NEW_DST
*
* PURPOSE : This procedure will get the start and end dates for the new daylight
*           savings time
*`
******************************************************************************/
   PROCEDURE get_new_dst(
      year_i        IN       VARCHAR2,
      dst_begin_o   OUT      DATE,
      dst_end_o     OUT      DATE
   );
END;
/
CREATE OR REPLACE PACKAGE BODY "ALL_DATE_CONVERT"
IS
 

/*****************************************************************************
* NAME : CONVERT_GMT_TO_LOCAL
*
* PURPOSE           : This procedure will convert the GMT to local time
*`
******************************************************************************/
   FUNCTION convert_gmt_to_local(
      date_i               IN   DATE,
      offset_i             IN   NUMBER,
      daylight_savings_i   IN   NUMBER
   )
      RETURN DATE
   IS
      gmtdate_o     DATE;
      dst_begin_o   DATE;
      dst_end_o     DATE;
     -- offset_i      NUMBER       := 0;
            offset_t      NUMBER       := 0;
      year_t        VARCHAR2(10) := TO_CHAR(date_i,
                                            'YYYY'
                                           );
   BEGIN
      --  SELECT dbtimezone into offset_i FROM dual;
       
      IF (date_i IS NOT NULL)
      THEN
-- Add offset to GMT time
         gmtdate_o    :=(date_i +(offset_i / 24));

         IF daylight_savings_i = 0
-- DST flag is ''0' return the date with offset
         THEN
            RETURN gmtdate_o;
         ELSE
          -- get the start and end dates for the NEW daylight savings date
--  Starts in second Sunday of MARCH and ends on the first Sunday in NOVEMBER
            IF (    (daylight_savings_i = 1)
                AND (TO_NUMBER(year_t) >= 2007))
            THEN
               get_new_dst(year_t,
                           dst_begin_o,
                           dst_end_o
                          );
            END IF;

            IF (    (date_i >= dst_begin_o)
                AND (date_i < dst_end_o))
            THEN
-- new time is in daylight savings and add 1 hour to new time
               gmtdate_o    :=(gmtdate_o +(1 / 24));
            ELSE
-- new time is not in daylight savings
               gmtdate_o    := gmtdate_o;
            END IF;
         END IF;

         RETURN gmtdate_o;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END;



/*****************************************************************************
* NAME : GET_NEW_DST
*
* PURPOSE : This procedure will get the start and end dates for the new daylight
*           savings time
*`
******************************************************************************/
   PROCEDURE get_new_dst(
      year_i        IN       VARCHAR2,
      dst_begin_o   OUT      DATE,
      dst_end_o     OUT      DATE
   )
   IS
   BEGIN
-- Second sunday in March. Summer begins at: 02:00:00
      dst_begin_o    := NEXT_DAY(TO_DATE('0301' || year_i || '02:00:00',
                                         'MMDDYYYYHH24:MI:SS'
                                        ) - 1,
                                 'SUNDAY'
                                ) + 7;
-- first sunday in november
      dst_end_o      := NEXT_DAY(TO_DATE('0111' || year_i || '02:00:00',
                                         'MMDDYYYYHH24:MI:SS'
                                        ) - 1,
                                 'SUNDAY'
                                );
   END;
END;
/
0
 
kfeinerAuthor Commented:
Here is what I have so far and Im getting an ora-01858 error (non-numberic char was found where numeric was expected)

create or replace function daylight_st(i_date IN DATE DEFAULT NULL)
  return boolean is
  DST_B boolean;
  v_date      DATE;
  dst_begin DATE;
  dst_end   DATE;
  v_year    DATE;
 
BEGIN
  IF i_date IS NULL THEN
    v_date := trunc(SYSDATE);
  ELSE
    v_date := trunc(i_date);
    END IF;
 
    v_year := trunc(i_date, 'YYYY');
 
    -- Second sunday in March. Summer begins at: 02:00:00
    dst_begin := NEXT_DAY(TO_DATE('0301' || v_year || '02:00:00',

                                    'MMDDYYYYHH24:MI:SS') - 1,
                            'SUNDAY') + 7;
 
    -- first sunday in november
    dst_end := NEXT_DAY(TO_DATE('0111' || v_year || '02:00:00',
                                  'MMDDYYYYHH24:MI:SS') - 1,
                          'SUNDAY');

  IF v_date >= dst_begin AND v_date < dst_end
  THEN
  DST_B := TRUE;
  ELSE
  DST_B := FALSE;
  END IF;
 
  RETURN(DST_B);

END dst;
0
 
sventhanCommented:
How do you call the function?

Where did you get the error?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sventhanCommented:
it works for me...

CREATE OR REPLACE function EGAINIM_PARTITION_DBO.daylight_st(i_date IN DATE DEFAULT NULL)
  return boolean is
  DST_B boolean;
  v_date      DATE;
  dst_begin DATE;
  dst_end   DATE;
 -- v_year    DATE;
      v_year        VARCHAR2(10) := TO_CHAR(i_date,
                                            'YYYY'
                                           );
BEGIN
  IF i_date IS NULL THEN
    v_date := trunc(SYSDATE);
  ELSE
    v_date := trunc(i_date);
    END IF;
 
   -- v_year := trunc(i_date, 'YYYY');
   
   

 
    -- Second sunday in March. Summer begins at: 02:00:00
    dst_begin := NEXT_DAY(TO_DATE('0301' || v_year || '02:00:00',

                                    'MMDDYYYYHH24:MI:SS') - 1,
                            'SUNDAY') + 7;
 
    -- first sunday in november
    dst_end := NEXT_DAY(TO_DATE('0111' || v_year || '02:00:00',
                                  'MMDDYYYYHH24:MI:SS') - 1,
                          'SUNDAY');

  IF v_date >= dst_begin AND v_date < dst_end
  THEN
  DST_B := TRUE;
  ELSE
  DST_B := FALSE;
  END IF;
 
  RETURN(DST_B);

END daylight_st;
/
0
 
sdstuberCommented:
CREATE OR REPLACE FUNCTION is_dst(p_date IN DATE DEFAULT SYSDATE)
    RETURN BOOLEAN DETERMINISTIC
IS
    c_uniform_time_act   CONSTANT DATE := TO_DATE('1966-04-24', 'yyyy-mm-dd');
    v_year                        PLS_INTEGER;
    v_start_of_dst                DATE;
    v_end_of_dst                  DATE;
BEGIN
    /*
                 Timeline of major events and laws to define or alter U.S. observation of Daylight Saving.
                 Since there was no standardization of observation nationwide until 1966 with the Uniform Time Act
                 is_dst will make no attempt to resolve "Prevailing" timezones prior to the last Sunday
                 in April of 1966 (April 24.)  The first two timeline entries below are merely for historical
                 interest and completeness, they have no impact on the functionality of the algorithm.

                 March 19, 1918 [S. 1854] [Public, No. 106.] Wilson
                 'An Act to preserve daylight and provide standard time for the United States'
                     Established US Timezones: Eastern, Central, Mountain, Pacific and Alaska and set 2am transitions
                     of Daylight Saving; but repealed after WWI, by overruling Wilson veto due to public opposition.

                 January 20, 1942 [S.2160] [Public Law 403] Roosevelt
                 'An Act To promote the national security and defense by establishing daylight saving time.'
                     Imposed year-round Daylight Saving for all of U.S. from Feb. 2, 1942 to Sep. 30, 1945

                 April 13, 1966 [Public Law 89-387] Johnson
                 'The Uniform Time Act of 1966' (15 U.S. Code Section 260a)
                     Created Daylight Saving Time to begin on the last Sunday of April and to end on the last Sunday of October.
                     First nationwide standard.  States could opt out by passing local laws, but this law stated the rules
                     for observation of any state that did participate.

                 December 15, 1973 [P.L. 93-182] Nixon
                 'Emergency Daylight Saving Time Energy Conservation Act of 1973'
                     Established an experimental extended Daylight Saving starting January 6, 1974 through April 27, 1975
                     Later amended by [P.L. 93-434] to allow resumption of Standard time for the period beginning October 27, 1974
                     and ending February 23, 1975 (thus 1975 also had an extended DST observation.)

                 October 8, 1986 [P.L. 99-359] Reagan
                 'Fire Prevention and Control Authorizations Act'
                     Amended the Uniform Time Act of 1966 to begin DST on the first Sunday in April.
                     The end of DST would remain at the last Sunday in October.

                 August 8, 2005 [ HR6] Bush
                 'Energy Policy Act of 2005'

                 Reference: http://webexhibits.org/daylightsaving/

     *************************************************************************************************************************
     */
    IF p_date IS NULL
    THEN
        RETURN NULL;
    END IF;

    --
    -- For dates prior to the Uniform Time Act of 1966 we can't produce a standardized algorithm
    --
    IF p_date < c_uniform_time_act
    THEN
        raise_application_error
            (-20000,
             'U.S. DST calculation not supported prior to The Uniform Time Act of 1966'
            );
    END IF;

    --
    -- first we need to determine the daylight/standard transition dates.
    --
    v_year    := TO_NUMBER(TO_CHAR(p_date, 'yyyy'));

    IF v_year = 1974
    THEN
        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 v_year = 1975
    THEN
        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 <= 1986
    THEN
        -- DST starts on Last Sunday in April in 1986 and prior
        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'
                    );
    ELSIF v_year < 2007
    THEN
        -- DST starts on First Sunday in April after 1986
        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'
                    );
    ELSE
        -- DST starts on Second Sunday in March starting 2007
        -- Because of leap years we don't know when February ends so use March 1st - 1 day
        -- to figure it out.
        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'
                    );
    END IF;

    RETURN     p_date >=(v_start_of_dst + 2 / 24)
           AND p_date <=(v_end_of_dst + 2 / 24);
END is_dst;
0
 
sdstuberCommented:
my version doesn't go with the midnight thresholds, it uses the real times of 2am,

but you can adjust for that easily enough
just change the return clause to....


RETURN     p_date >= v_start_of_dst
           AND p_date <= v_end_of_dst;
END is_dst;
0
 
kfeinerAuthor Commented:
hey sdstuber... I used your template to mesh with my own code.  Do you see anything wrong with my version?  Im confused with your method of starting at 2AM... im thinking of the order of operations and it looks as if it adds 12 hours to v_start_of_dst and v_end_of_dst.

CREATE OR REPLACE FUNCTION dst(i_date IN DATE DEFAULT SYSDATE)
    RETURN BOOLEAN
IS
    v_year                        PLS_INTEGER;
    v_start_of_dst                DATE;
    v_end_of_dst                  DATE;
    v_date                        DATE;
BEGIN
   

    IF i_date IS NULL
    THEN
    v_date := trunc(sysdate);
    ELSE
    v_date := i_date;
    END IF;

    -- to determine the daylight/standard transition dates.
    --
    v_year    := TO_NUMBER(TO_CHAR(v_date, 'yyyy'));

   
        -- DST starts on Second Sunday in March starting 2007
        -- Because of leap years we don't know when February ends so use March 1st - 1 day
        -- to figure it out.
        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'
                    );
   

    RETURN     v_date >=(v_start_of_dst + 2 / 24)
           AND v_date <=(v_end_of_dst + 2 / 24);
END dst;
0
 
sdstuberCommented:
The reason I start and end at 2am is because that's when the DST changes occur.

i_date is of type date so has not only a calendar day but also a time component.

So that does produce some potentially erroneous results, either passing in a time or
truncating sysdate, because
1am on Nov 4, 2007 is still Daylight Saving Time.  3am Nov 4, 2007 is Standard Time.

I'm curious why you took out all the other time ranges.
If you're going to do that, then you should add a check in the code to make sure it won't accept any dates prior to 2007.

What happens if somebody calls your function like DST(to_date('2005-03-31','yyyy-mm-dd'))?

DST will return the wrong results.  It will erroneously return TRUE when it should return FALSE because the March transition didn't start until 2007, before that it April (except the 2 exceptions of 1974/75).


You should raise an exception (like I did with dates prior to the Uniform Time Act of 1966) or return NULL or something if your inputs are prior to 2007 otherwise it's not a reliable function.
0
 
sdstuberCommented:
I'll throw one additional caveat and that is there is a period that could be reported either way.

1am to 2am on the DST to Standard day happens twice.

The first time you're still in DST, the second time your in Standard time.

How you want to handle that may depend on your vacation.
0

Featured Post

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!

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