2

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.

Viswa Peesapati
  • 23
  • 3

1 Answers1

2

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.

See Support for Daylight Saving Time

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I understand and agree with providing both US/Mountain (TZR) and MST/MDT (TZD) to eliminate the confusion. I also like having ERROR_ON_OVERLAP_TIME setting to TRUE, just to avoid default value of MST. – Viswa Peesapati Apr 21 '21 at 14:19