Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone [CORE6276] #6518

Closed
firebird-automations opened this issue Apr 5, 2020 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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;

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

SQL:2016-2 (section 4.6.2) says:

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Earlier in my previous comment I said I thought you were incorrect but re-reading I agree with it.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

As discussed in fb-devel, we will not change the behavior.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants