asked on

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.

sventhan

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;
/

kfeiner

ASKER

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;

sventhan

How do you call the function?

Where did you get the error?

sventhan

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;
/

Sean Stuber

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;

Sean Stuber

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;

kfeiner

ASKER

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;

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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.