In Oracle, how can I detect the date on which daylight savings time begins / ends?

Question

Is there a way in Oracle to select the date on which daylight savings will switch over for my locale?

Something vaguely equivalent to this would be nice:

SELECT CHANGEOVER_DATE
FROM SOME_SYSTEM_TABLE
WHERE DATE_TYPE = 'DAYLIGHT_SAVINGS_CHANGEOVER'
  AND TO_CHAR(CHANGEOVER_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY');  -- in the current year

Edit: I was hoping for a solution that would not require changes when Congress adjusts DST laws, as they did in 2007. The posted solutions will work, though.

Answer 1

Old question but here's a new answer. Use 08-MAR for the first date since that skips the first week

--Start of DST
select next_day(to_date('08-MAR-' || to_char(sysdate, 'YYYY')), 'SUN') from dual

--End of DST
select next_day(to_date('01-NOV-' || to_char(sysdate, 'YYYY')), 'SUN') from dual

Answer 2

To improve on Leigh Riffel's answer, this is much simpler with the same logic:

Function DaylightSavingTimeStart (p_Date IN Date)
Return Date Is
Begin
   Return NEXT_DAY(TO_DATE(to_char(p_Date,'YYYY') || '/03/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') + 7;
End;

Function DaylightSavingTimeEnd (p_Date IN Date)
Return Date Is
Begin
   Return NEXT_DAY(TO_DATE(to_char(p_Date,'YYYY') || '/11/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN');
End;

Answer 3

Here's my version of the above. It's advantage is that it does not need a second 'alter session set time zone', and can be used more easily from an application. You create the stored function, and then you simply use: ALTER SESSION SET time_zone='Asia/Jerusalem'; select GetDSTDates(2012,1) DSTStart,GetDSTDates(2012,2) DSTEnd,SessionTimeZone TZ from dual;

which will return the dst start date,dst end date, timezone for the specified year.

create or replace function GetDSTDates
(
  year integer,
  GetFrom integer
)
return Date
as
  cursor c is
    select 12-to_number(to_char(LocalTimeZone at time zone '+00:00','HH24')) offset,
    min(to_char(LocalTimeZone at time zone '+00:00','DD/MM/YYYY')) fromdate,
    max(to_char(LocalTimeZone at time zone '+00:00','DD/MM/YYYY')) todate 
        from (
        SELECT cast((to_date('01/01/'||to_char(year)||'12:00:00','MM/DD/YYYYHH24:MI:SS')+rownum-1) as timestamp with local time zone) LocalTimeZone
        FROM dual CONNECT BY rownum<=365
        )
    group by 12-to_number(to_char(LocalTimeZone at time zone '+00:00','HH24'));
  dstoffset integer;
  offset integer;
  dstfrom date;
  dstto date;
begin
  offset := 999;
  dstoffset := -999;
  for rec in c
  loop 
    if rec.offset<offset
    then
      offset := rec.offset;
    end if;
    if rec.offset>dstoffset
    then
      dstoffset := rec.offset;
      dstfrom := to_date(rec.fromdate,'DD/MM/YYYY');
      dstto :=to_date(rec.todate,'DD/MM/YYYY');
    end if;
  end loop;
  if (offset<999 and dstoffset>-999 and offset<>dstoffset)
  then
    if GetFrom=1
    then
      return dstfrom;
    else 
      return dstto;
    end if;
  else
    return null;
  end if;
end;
/
ALTER SESSION SET time_zone='Asia/Jerusalem';
select GetDSTDates(2012,1) DSTStart,
       GetDSTDates(2012,2) DSTEnd,
       SessionTimeZone TZ from dual;

Answer 4

Instead of looping to get the next sunday you can also use the next_day(date, 'SUN') function of oracle.

Answer 5

Here is a way to use Oracles internal knowledge of whether a timezone observes daylight saving time or not to determine the start and end of it. Aside from the complexity and general strangeness of it, it requires two timezones to be know have identical times when daylight saving time is not in effect and different times when it is. As such it is resilient to congressional changes in when daylight saving time occurs (assuming your database is up to date with the patches), but is not resilient to regional changes effecting the timezones keyed off of. With those warnings, here is what I have.

ALTER SESSION SET time_zone='America/Phoenix';
DROP TABLE TimeDifferences;
CREATE TABLE TimeDifferences(LocalTimeZone TIMESTAMP(0) WITH LOCAL TIME ZONE);
INSERT INTO TimeDifferences
(
   SELECT to_date('01/01/' || to_char(sysdate-365,'YYYY') || '12:00:00','MM/DD/YYYYHH24:MI:SS')+rownum-1 
   FROM dual CONNECT BY rownum<=365
);
COMMIT;

ALTER SESSION SET time_zone='America/Edmonton';
SELECT LocalTimeZone-1 DaylightSavingTimeStartAndEnd
FROM
(
   SELECT LocalTimeZone, 
      to_char(LocalTimeZone,'HH24') Hour1,
      LEAD(to_char(LocalTimeZone,'HH24')) OVER (ORDER BY LocalTimeZone) Hour2 
   FROM TimeDifferences
)
WHERE Hour1 <> Hour2;  

I told you it was strange. The code only figures out the day of the change, but could be enhanced to show the hour. Currently it returns 09-MAR-08 and 02-NOV-08. It is also sensitive to the time of year it is run, which is why I had to do the -365...+365. All in all I don't recommend this solution, but it was fun to investigate. Maybe someone else has something better.

Answer 6

We use the following two functions to calculate the start and end dates for any given year (post 2007, US).

Function DaylightSavingTimeStart (p_Date IN Date)
Return Date Is
   v_Date       Date;
   v_LoopIndex  Integer;
Begin
   --Set the date to the 8th day of March which will effectively skip the first Sunday.
   v_Date := to_date('03/08/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
   --Advance to the second Sunday.
   FOR v_LoopIndex IN 0..6 LOOP
      If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
         Return v_Date + v_LoopIndex;
      End If;
   END LOOP;
End;

Function DaylightSavingTimeEnd (p_Date IN Date)
Return Date Is
   v_Date       Date;
   v_LoopIndex  Integer;
Begin
   --Set Date to the first of November this year
   v_Date := to_date('11/01/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
   --Advance to the first Sunday
   FOR v_LoopIndex IN 0..6 LOOP
      If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
         Return v_Date + v_LoopIndex;
      End If;
   END LOOP;
End;

There is probably a simpler way to do it, but these have worked for us. Of course this query doesn't know whether daylight saving time is observed for where you are. For that you will need location data.

Answer 7

In the United States, Daylight Savings Time is defined as beginning on the second Sunday in March, and ending on the first Sunday in November, for the areas that observe DST, for years after 2007.

I don't think there's an easy way to get this information from Oracle, but based on the standard definition, you should be able to write a stored procedure that calculates the beginning and ending date using the Doomsday Algorithm.