Agfdhyk

I have a strange behavior of Oracle DB with TIMESTAMP and daylight saving.

The following query produces different results when grouping by different columns and it is not clear why.

When grouping by my_date_ny_ts it returns two different rows and when by my_date_ny_ts_tz - the result is only one row (the right one for me).

Please note the values are for the 11/04/2018 00:00:00 -05:00 and 11/04/2018 00:00:00 -06:00 that are converted to the New_York timezone to 11-04-2018 01:00 and 02:00 EDT which are actually 01:00 EDT and 01:00 EST.

I understand why those values are different, but they should be equal after converting them to the TIMESTAMP without time zone data type (the my_date_ny_ts column), since this type doesn't contain any information about timezone and daylight saving status (see the tdz_ny_ts values). Only after I convert the values back to the TIMESTAMP WITH TIMEZONE type (the my_date_ny_ts_tz) they become equal.
I don't need a workaround (already have it), just wondering if this behavior is an Oracle bug or by misunderstanding:

select count(*), my_date_ny_ts_tz from (



SELECT

 mydate, -- timestamp with timezone

 to_char( mydate, 'TZD') as tdz, -- daylight savings flag - VALUES are NULL because timezone is an offset

 mydate AT TIME ZONE 'America/New_York' AS mydate_ny, -- timestamp with timezone in EST timezone

 to_char( mydate AT TIME ZONE 'America/New_York', 'TZD') as tdz_ny, --timestamp with timezone in EST timezone -  daylight savings flag - RETURNS EDT FOR ONE EST FOR SECOND - RIGHT

cast(mydate AT TIME ZONE 'America/New_York' as timestamp)  as my_date_ny_ts,--cast to timestamp without timezone - GROUP BY RETURNS TWO ROWS - BUG?

to_char( cast(mydate AT TIME ZONE 'America/New_York' as timestamp) , 'TZD') as tdz_ny_ts,--cast to timestamp without timezone -  daylight savings flag - both values are null so why the group by on the prev field doesn't work?

 cast(mydate AT TIME ZONE 'America/New_York' as timestamp) AT TIME ZONE 'America/New_York' as my_date_ny_ts_tz,--cast back to timestamp with timezone in EST timezone - NOW GROUP BY RETURNS ONE ROW

 to_char( cast(mydate AT TIME ZONE 'America/New_York' as timestamp) AT TIME ZONE 'America/New_York', 'TZD') as tdz_ny_ts_tz--daylight savings flag of the prev field - both are EST - RIGHT

 FROM

     (

         SELECT

         to_timestamp_tz('11/04/2018 00:00:00 -05:00','mm/dd/yyyy hh24:mi:ss TZH:TZM') AS mydate

     FROM

         dual

     UNION

     SELECT

         to_timestamp_tz('11/04/2018 00:00:00 -06:00','mm/dd/yyyy hh24:mi:ss TZH:TZM') AS mydate

     FROM

         dual

 )

 ) group by my_date_ny_ts_tz

My version is the following, but it happens in 12c as well:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

"CORE   11.2.0.4.0  Production"

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Thanks

Could be a bug in Oracle. You can shorten the query to this:

SELECT 

    TO_CHAR(CAST(TIMESTAMP '2018-11-04 00:00:00 -05:00' AT TIME ZONE 'America/New_York' AS TIMESTAMP), 'yyyy-mm-dd hh24:mi:ss') AS my_date_ny_ts,

    DUMP(CAST(TIMESTAMP '2018-11-04 00:00:00 -05:00' AT TIME ZONE 'America/New_York' AS TIMESTAMP)) AS my_date_ny_ts_dump

FROM DUAL 

UNION ALL

SELECT

    TO_CHAR(CAST(TIMESTAMP '2018-11-04 00:00:00 -06:00' AT TIME ZONE 'America/New_York' AS TIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'),

    DUMP(CAST(TIMESTAMP '2018-11-04 00:00:00 -06:00' AT TIME ZONE 'America/New_York' AS TIMESTAMP))

FROM DUAL;





+--------------------------------------------------------------------------------------+

|MY_DATE_NY_TS      |MY_DATE_NY_TS_DUMP                                                |

+--------------------------------------------------------------------------------------+

|2018-11-04 01:00:00|Typ=187 Len=20: 226,7,11,4,1,0,0,0,0,0,0,0,252,0,3,0,100,0,11,3   |

|2018-11-04 01:00:00|Typ=187 Len=20: 226,7,11,4,1,0,0,48,0,0,0,0,251,0,3,44,100,0,48,44|

+--------------------------------------------------------------------------------------+

As you see the timestamp values are the same, however the DUMP() values are different, i.e. you get two rows if you make GROUP BY.

You can run it slightly different. Actually I would expect the same result as above (no matter wether you consider that as correct or not) but it is different:

WITH t AS

    (SELECT 

        CAST(TIMESTAMP '2018-11-04 00:00:00 -05:00' AT TIME ZONE 'America/New_York' AS TIMESTAMP) AS my_date_ny_ts

    FROM DUAL 

    UNION ALL

    SELECT

        CAST(TIMESTAMP '2018-11-04 00:00:00 -06:00' AT TIME ZONE 'America/New_York' AS TIMESTAMP)

    FROM DUAL)

SELECT TO_CHAR(my_date_ny_ts, 'yyyy-mm-dd hh24:mi:ss') AS my_date_ny_ts,

    DUMP(my_date_ny_ts) AS my_date_ny_ts_dump

FROM t;



    +--------------------------------------------------------------------------------------+

|MY_DATE_NY_TS      |MY_DATE_NY_TS_DUMP                                                |

+--------------------------------------------------------------------------------------+

|2018-11-04 01:00:00|Typ=180 Len=7: 120,118,11,4,2,1,1                                 |

|2018-11-04 01:00:00|Typ=180 Len=7: 120,118,11,4,2,1,1                                 |

+--------------------------------------------------------------------------------------+

This looks strange to me. Although I made CAST(... AS TIMESTAMP) for both, once I get Typ=187 and once I get Typ=180 in DUMP.

Looks like the SQL type TIMESTAMP 180 (see Oracle Built-In Data Types) behaves different than the PL/SQL type TIMESTAMP 187 (see PACKAGE SYS.dbms_types) - but I don't know why.

For the workaround I would suggest to use function SYS_EXTRACT_UTC(...) rather than CAST(... AS TIMESTAMP).