In Oracle, how can I determine the start / end date of daylight saving time?

Question

Is there a way in Oracle to select daylight saving time 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 a change when Congress would regulate the laws of the DST, as it was in 2007. However, hosted solutions will work.

Answer 1

We use the following two functions to calculate 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;

, , . , , , . .

Answer 2

, :

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

, , , next_day (date, 'SUN) . ​​

Answer 4

, , , DST, 2007 .

, Oracle, , , Doomsday.

Answer 5

, , . , , , , , . , , ( , ​​ ), , . .

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;  

, . , , . 09-MAR-08 02-NOV-08. , , -365... + 365. , . , - - .

Answer 6

. , " " . , : ALTER SESSION SET time_zone = '/'; GetDSTDates (2012,1) DSTStart, GetDSTDates (2012,2) DSTEnd, SessionTimeZone TZ dual;

which will return the dst start date, dst end date, time zone 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;