Good afternoon.
I understand that there is "each second of each minute of one specific hour" is repeated (1 AM - 1:59:59 AM) on first Sunday of November (Closing day of Daylight Saving time). So, duration from 0:00 AM (midnight) to 3 AM is 4 hours on that day.
SELECT TO_TIMESTAMP_TZ('2021-11-07 03:00:00 US/Mountain', 'yyyy-mm-dd hh24:mi:ss TZR') - TO_TIMESTAMP_TZ('2021-11-07 00:00:00 US/Mountain', 'yyyy-mm-dd hh24:mi:ss TZR') FROM DUAL;
The above query is returning 4 hours as expected.
Here is my question - I want to basically differentiate/represent the two occurrences of 1 AM (or any time between 1 AM, and 1:59:59 AM). How can I do? (I am using Oracle 12.1)
BTW, this following query is resulting 1 hour 30 minutes, so '2021-11-07 01:30:00 MST' represents the second instance of 1:30 AM. In the same manner, I was expecting '2021-11-07 01:30:00 MDT' to be the first instance, however it is resulting ORA-01882: timezone region not found. BTW, I prefer to have US/Mountain (or something like that) for region, rather MST vs. MDT
SELECT TO_TIMESTAMP_TZ('2021-11-07 03:00:00 MST', 'yyyy-mm-dd hh24:mi:ss TZR') - TO_TIMESTAMP_TZ('2021-11-07 01:30:00 MST', 'yyyy-mm-dd hh24:mi:ss TZR') FROM DUAL;
Sorry if I confused you. Please let me know if any questions. Any help?
Thank you
Viswa
Added later: I think I found the answer: We need to use TZD flag, and use MST/MDT values. I did not like that as I prefer to use the region (such as US/Mountain). So any enhancements will be appreciated.
SELECT TO_TIMESTAMP_TZ('2021-11-07 03:00:00 US/Mountain', 'yyyy-mm-dd hh24:mi:ss TZR') - TO_TIMESTAMP_TZ('2021-11-07 00:00:00 US/Mountain', 'yyyy-mm-dd hh24:mi:ss TZR') FROM DUAL;
-- 4 hours:00 minutes, as expected
SELECT TO_TIMESTAMP_TZ('2021-11-07 03:00:00 MST', 'yyyy-mm-dd hh24:mi:ss TZD') - TO_TIMESTAMP_TZ('2021-11-07 01:30:00 MDT', 'yyyy-mm-dd hh24:mi:ss TZD') FROM DUAL;
-- 2:30 minutes -- So any values ranging from 1:00 to 1:59:59 with a time zone of MDT are the first instance values.
SELECT TO_TIMESTAMP_TZ('2021-11-07 03:00:00 MST', 'yyyy-mm-dd hh24:mi:ss TZD') - TO_TIMESTAMP_TZ('2021-11-07 01:30:00 MST', 'yyyy-mm-dd hh24:mi:ss TZD') FROM DUAL;
-- 1 hour:30 minutes -- So any values ranging from 1:00 to 1:59:59 with a time zone of MST are the second instance values.
Value TIMESTAMP '2021-11-07 01:00:00 US/Mountain' is ambiguous, it could be
2021-11-07 01:00:00-06:00 or 2021-11-07 01:00:00-07:00
If you don't specify the daylight-saving-time status then Oracle defaults to the standard time, (MST in your case)
You need to provide both, the timezone region and the Daylight Saving Time information, i.e. TO_TIMESTAMP_TZ('2021-11-07 01:00:00 US/Mountain MST', 'yyyy-mm-dd hh24:mi:ss TZR TZD') or TO_TIMESTAMP_TZ('2021-11-07 01:00:00 US/Mountain MDT', 'yyyy-mm-dd hh24:mi:ss TZR TZD')
Note, if you alter your session with ALTER SESSION SET ERROR_ON_OVERLAP_TIME = TRUE; then for ambiguous times like TIMESTAMP '2021-11-07 01:00:00 US/Mountain' Oracle does not default to standard time but raise an error:
ORA-01883: overlap was disabled during a region transition
Don't mistake "Time zone region" (TRZ) with "Daylight saving information" (TZD), however MST can mean both:
SELECT *
FROM V$TIMEZONE_NAMES
WHERE TZABBREV in ('MST', 'MDT');
Calculations of TIMESTAMP WITH TIME ZONE values are internally always performed on UTC times.