Oracle Database Query with Time Zones-CodePudding

I'm new to Oracle,I found a query which is somewhat confusing. Can someone please explain what is the meaning of below , what is being done to ENDTIME.

SYS_EXTRACT_UTC(from_tz(CAST(END_TIME AS TIMESTAMP),to_char(SYSTIMESTAMP,'TZH:TZM'))) as endTime

CodePudding user response：

Presumably END_TIME is a DATE column.

Working outwards from the innermost functions:

CAST(END_TIME AS TIMESTAMP) converts the DATE to a TIMESTAMP - this adds fractional seconds (which will be zero), but no time zone information. For example, a date of 2022-01-04 06:30:45 would become 2022-01-04 06:30:45.000.
to_char(SYSTIMESTAMP,'TZH:TZM') gets the current system timestamp, which includes the system (not session) time zone, and converts just the time zone part to a string representation of that time zone offset, such as ' 08:00'.
from_tz(CAST(END_TIME AS TIMESTAMP),to_char(SYSTIMESTAMP,'TZH:TZM')) converts the TIMESTAMP value from the first bullet to a TIMESTAMP WITH TIME ZONE, using the time zone offset from the second bullet, effectively doing from_tz(timestamp '2022-01-04 06:30:45.000', ' 08:00'), so the value now becomes 2022-01-04 06:30:45.000 08:00. (The overall expression would appear to work without this step; but then you would be doing an implicit conversion using the session time zone, which could give a different answer, and different answers for different users.)
SYS_EXTRACT_UTC(from_tz(CAST(END_TIME AS TIMESTAMP),to_char(SYSTIMESTAMP,'TZH:TZM'))) converts that time zone-aware value to its UTC equivalent, as a plain timestamp, effectively doing SYS_EXTRACT_UTC(timestamp '2022-01-04 06:30:45.000 08:00'), so the value now becomes 2022-01-03 22:30:45.000
as endTime just gives that column expression an alias in the final result.

This still has an issue if the system has daylight savings time. Because the conversion is using the current system time zone offset, it will get different results if you run it at different times of year. Without knowing the system's time zone region, rather than just the offset, there isn't much you can do about that; and you can't get the region from systimestamp.

CodePudding user response：

FROM_TZ() attaches a time zone to a TIMESTAMP without time zone.
FROM_TZ() works only on TIMESTAMP. END_TIME seems to be a DATE data type, that's the reason for CAST(END_TIME AS TIMESTAMP) (Note, neither DATE nor TIMESTAMP contain any time zone information)
TO_CHAR(SYSTIMESTAMP,'TZH:TZM') returns the time zone of the database server's operating system
SYS_EXTRACT_UTC() transforms a TIMESTAMP WITH TIME ZONE value to UTC Time

To summarize: END_TIME is a DATE value at the time zone of the database server's operating system. The statement converts it into UTC time.

As mentioned correctly by Alex, this query may have an issue with daylight saving times, see this example to visualize:

SELECT 
    SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP,'TZH:TZM'))) AS END_TIME_1,
    SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE 150 AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP,'TZH:TZM'))) AS END_TIME_2,
    SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'Europe/Zurich')) AS END_TIME_3,
    SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE 150 AS TIMESTAMP), 'Europe/Zurich')) AS END_TIME_4 
FROM dual;

---------------------------------------------------------------------------------------- 
| END_TIME_1          | END_TIME_2          | END_TIME_3          | END_TIME_4          |
 --------------------------------------------------------------------------------------- 
| 05.01.2022 13:59:35 | 04.06.2022 13:59:35 | 05.01.2022 13:59:35 | 04.06.2022 12:59:35 |
 ---------------------------------------------------------------------------------------