Issue Details (XML | Word | Printable)

Key: CORE-6276
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone

Created: 05/Apr/20 12:07 AM   Updated: 07/Apr/20 06:13 AM
Component/s: Engine
Affects Version/s: 4.0 Beta 1
Fix Version/s: None

QA Status: No test


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 05/Apr/20 06:17 AM - edited
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.
"""

Mark Rotteveel added a comment - 05/Apr/20 06:21 AM
Earlier in my previous comment I said I thought you were incorrect but re-reading I agree with it.

Adriano dos Santos Fernandes added a comment - 06/Apr/20 11:24 AM
As discussed in fb-devel, we will not change the behavior.