Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone ------------------------------------------------------------------------------------------------------------------------
Key: CORE-6276 URL: http://tracker.firebirdsql.org/browse/CORE-6276 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 4.0 Beta 1 Reporter: Adriano dos Santos Fernandes Firebird (as well PostgreSQL) uses the session time zone when converting from WITH-TZ to WITHOUT-TZ types. Example: set time zone '-03:00'; SQL> select cast(timestamp '2020-04-04 10:00:00 -05:00' as timestamp) from rdb$database; CAST ========================= 2020-04-04 12:00:00.0000 When converting it back to TIMESTAMP WITH-TZ (not altering the session time zone), we get an equal (equivalent) timestamp (which different offset): SQL> select cast(timestamp '2020-04-04 12:00:00.0000' as timestamp with time zone) from rdb$database; CAST ========================================================= 2020-04-04 12:00:00.0000 -03:00 SQL> select timestamp '2020-04-04 12:00:00.0000 -03:00' = timestamp '2020-04-04 10:00:00 -05:00' from rdb$database; ======= <true> However the standard SQL says that conversion from TIMESTAMP WITH-TZ to TIMESTAMP WITHOUT-TZ should be "SV.UTC + SV.TZ" (SV is the source value). In practice this means to drop the time zone. Oracle implementation also respects (and documents in https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm) this behavior: "Insert the same date and time as a TIMESTAMP WITH TIME ZONE literal. Oracle converts it to a TIMESTAMP value, which means that the time zone information is dropped.". Also accordingly to the standard the conversion from WITHOUT-TZ to WITH-TZ is "TV.UTC = SV - STZD; TV.TZ = STZD" (TV is target value; STZD is the session time zone). So in this respect we are correct and then converting WITH-TZ -> WITHOUT-TZ -> WITH-TZ does not necessarily produces an equivalent value. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel