code_junkie

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

Nov 12th, 2011
59
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.78 KB | None | 0 0
  1. SELECT CHANGEOVER_DATE
  2. FROM SOME_SYSTEM_TABLE
  3. WHERE DATE_TYPE = 'DAYLIGHT_SAVINGS_CHANGEOVER'
  4. AND TO_CHAR(CHANGEOVER_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY'); -- in the current year
  5.  
  6. Function DaylightSavingTimeStart (p_Date IN Date)
  7. Return Date Is
  8. v_Date Date;
  9. v_LoopIndex Integer;
  10. Begin
  11. --Set the date to the 8th day of March which will effectively skip the first Sunday.
  12. v_Date := to_date('03/08/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
  13. --Advance to the second Sunday.
  14. FOR v_LoopIndex IN 0..6 LOOP
  15. If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
  16. Return v_Date + v_LoopIndex;
  17. End If;
  18. END LOOP;
  19. End;
  20.  
  21. Function DaylightSavingTimeEnd (p_Date IN Date)
  22. Return Date Is
  23. v_Date Date;
  24. v_LoopIndex Integer;
  25. Begin
  26. --Set Date to the first of November this year
  27. v_Date := to_date('11/01/' || to_char(p_Date,'YYYY') || '02:00:00 AM','MM/DD/YYYY HH:MI:SS PM');
  28. --Advance to the first Sunday
  29. FOR v_LoopIndex IN 0..6 LOOP
  30. If (RTRIM(to_char(v_Date + v_LoopIndex,'DAY')) = 'SUNDAY') Then
  31. Return v_Date + v_LoopIndex;
  32. End If;
  33. END LOOP;
  34. End;
  35.  
  36. ALTER SESSION SET time_zone='America/Phoenix';
  37. DROP TABLE TimeDifferences;
  38. CREATE TABLE TimeDifferences(LocalTimeZone TIMESTAMP(0) WITH LOCAL TIME ZONE);
  39. INSERT INTO TimeDifferences
  40. (
  41. SELECT to_date('01/01/' || to_char(sysdate-365,'YYYY') || '12:00:00','MM/DD/YYYYHH24:MI:SS')+rownum-1
  42. FROM dual CONNECT BY rownum<=365
  43. );
  44. COMMIT;
  45.  
  46. ALTER SESSION SET time_zone='America/Edmonton';
  47. SELECT LocalTimeZone-1 DaylightSavingTimeStartAndEnd
  48. FROM
  49. (
  50. SELECT LocalTimeZone,
  51. to_char(LocalTimeZone,'HH24') Hour1,
  52. LEAD(to_char(LocalTimeZone,'HH24')) OVER (ORDER BY LocalTimeZone) Hour2
  53. FROM TimeDifferences
  54. )
  55. WHERE Hour1 <> Hour2;
Add Comment
Please, Sign In to add comment