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).