I needed a way to find if a given date is within a DST date range. We observe DST here, so I needed to know if I needed to adjust the time or not. The main purpose was so I could convert a date/time to GMT, which does not observe DST.
First column is current date/time
DST_START is the day DST starts
DST_END is the day DST ends
DST_CHECK will be “Y” if we are currently in DST, “N” if not.
CURRENT_TIME_GMT converts the current time to what it would be in GMT
SELECT SYSDATE, NEXT_DAY ( TO_DATE (TO_CHAR (SYSDATE, 'YYYY') || '/03/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') + 7 dst_start, NEXT_DAY ( TO_DATE (TO_CHAR (SYSDATE, 'YYYY') || '/11/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') dst_end, CASE WHEN SYSDATE >= NEXT_DAY ( TO_DATE ( TO_CHAR (SYSDATE, 'YYYY') || '/03/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') + 7 AND SYSDATE < NEXT_DAY ( TO_DATE ( TO_CHAR (SYSDATE, 'YYYY') || '/11/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') THEN 'Y' ELSE 'N' END AS dst_check, NEW_TIME ( SYSDATE, CASE WHEN SYSDATE >= NEXT_DAY ( TO_DATE ( TO_CHAR (SYSDATE, 'YYYY') || '/03/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') + 7 AND SYSDATE < NEXT_DAY ( TO_DATE ( TO_CHAR (SYSDATE, 'YYYY') || '/11/01 02:00 AM', 'YYYY/MM/DD HH:MI AM') - 1, 'SUN') THEN 'CDT' ELSE 'CST' END, 'GMT') AS current_time_gmt FROM DUAL;
To give credit, I used the info from:
https://www.techonthenet.com/oracle/functions/new_time.php
https://stackoverflow.com/questions/287563/in-oracle-how-can-i-detect-the-date-on-which-daylight-savings-time-begins-end