You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
However the standard SQL says that conversion from TIMESTAMP WITH-TZ to TIMESTAMP WITHOUT-TZ should be "SV.UTC + http://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; http://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.
The text was updated successfully, but these errors were encountered:
"""
For the convenience of users, whenever a datetime value with time zone is to be implicitly derived from one
without (for example, in a simple assignment operation), SQL assumes the value without time zone to be local,
subtracts the current default time zone displacement of the SQL-session from it to give UTC, and associates
that time zone displacement with the result.
Conversely, whenever a datetime value without time zone is to be implicitly derived from one with, SQL
assumes the value with time zone to be UTC, adds the time zone displacement to it to give local time, and the
result, without any time zone displacement, is local.
"""
Currently the conversion ends up to the value in current session time zone instead of source value time zone. AFAIU from previous comments this behavior violates SQL standard. I suggest to reopen the ticket.
Submitted by: @asfernandes
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;
=========================
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;
=========================================================
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 + http://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; http://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.
The text was updated successfully, but these errors were encountered: