FROM_TZ in where clause is not returning correct results-CodePudding

Why the below sql does not return 2 rows?

WITH dates AS (
    SELECT TO_DATE('17/01/2023 11:42', 'dd/mm/yyyy hh24:mi') dst_time FROM dual 
    UNION ALL 
    SELECT TO_DATE('17/01/2023 17:35', 'dd/mm/yyyy hh24:mi') dst_time FROM dual
)
SELECT FROM_TZ(CAST(TO_DATE('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' begin_time,  
       FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' end_time,
      dst_time
FROM dates
WHERE dst_time BETWEEN FROM_TZ(CAST(to_date('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' 
                    AND FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT'  
ORDER BY dst_time DESC;

Note that FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' returns 10PM, but the result is not returning "17/01/2023 17:35" row. Very strange.

Can some one help me out?

Thanks.

CodePudding user response：

Problem is the dst_time are DATE values:

WITH dates AS (
    SELECT TO_DATE('17/01/2023 11:42', 'dd/mm/yyyy hh24:mi') dst_time FROM dual 
    UNION ALL 
    SELECT TO_DATE('17/01/2023 17:35', 'dd/mm/yyyy hh24:mi') dst_time FROM dual
) ...

Then you compare these DATE values with a TIMESTAMP WITH TIME ZONE values, i.e. the DATE dst_time value is implicitly converted to TIMESTAMP WITH TIME ZONE by using

FROM_TZ(CAST(dst_time AS TIMESTAMP), SESSIONTIMEZONE)

So, the result of your query depends on your current sessions SESSIONTIMEZONE which is most likely not US/EASTERN

SELECT FROM_TZ(CAST(TO_DATE('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' begin_time,  
       FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' end_time,
      dst_time
FROM dates
WHERE FROM_TZ(CAST(dst_time AS TIMESTAMP), SESSIONTIMEZONE)
     BETWEEN FROM_TZ(CAST(to_date('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') 
         AND FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN')
ORDER BY dst_time DESC;

As stated in my comment AT TIME ZONE 'GMT' in not needed, comparison is done in UTC anyway.

CodePudding user response：

Either explicitly cast your timestamps to dates in the WHERE filter:

WITH dates (dst_time) AS (
    SELECT DATE '2023-01-17'   INTERVAL '11:42' HOUR TO MINUTE FROM DUAL UNION ALL 
    SELECT DATE '2023-01-17'   INTERVAL '17:35' HOUR TO MINUTE FROM DUAL
)
SELECT TIMESTAMP '2023-01-17 00:00:00 US/EASTERN' AT TIME ZONE 'GMT' begin_time,  
       TIMESTAMP '2023-01-17 17:00:00 US/EASTERN' AT TIME ZONE 'GMT' end_time,
       dst_time
FROM   dates
WHERE  dst_time BETWEEN CAST(TIMESTAMP '2023-01-17 00:00:00 US/EASTERN' AT TIME ZONE 'GMT' AS DATE)
                    AND CAST(TIMESTAMP '2023-01-17 17:00:00 US/EASTERN' AT TIME ZONE 'GMT' AS DATE)
ORDER BY dst_time DESC;

Or cast your dates to timestamps in the GMT time zone:

WITH dates (dst_time) AS (
    SELECT DATE '2023-01-17'   INTERVAL '11:42' HOUR TO MINUTE FROM DUAL UNION ALL 
    SELECT DATE '2023-01-17'   INTERVAL '17:35' HOUR TO MINUTE FROM DUAL
)
SELECT TIMESTAMP '2023-01-17 00:00:00 US/EASTERN' AT TIME ZONE 'GMT' begin_time,  
       TIMESTAMP '2023-01-17 17:00:00 US/EASTERN' AT TIME ZONE 'GMT' end_time,
       dst_time
FROM   dates
WHERE  FROM_TZ(CAST(dst_time AS TIMESTAMP), 'GMT')
         BETWEEN TIMESTAMP '2023-01-17 00:00:00 US/EASTERN' AT TIME ZONE 'GMT'
             AND TIMESTAMP '2023-01-17 17:00:00 US/EASTERN' AT TIME ZONE 'GMT'
ORDER BY dst_time DESC;

Or alter the session time zone to be UTC (or GMT) and then run your query:

ALTER SESSION SET TIME_ZONE = 'UTC';

fiddle