Issue Details (XML | Word | Printable)

Key: CORE-6271
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Mark Rotteveel
Votes: 0
Watchers: 4
Operations

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

Bind time with time zone to legacy produces wrong values

Created: 29/Mar/20 11:43 AM   Updated: 08/May/20 11:40 AM
Component/s: Engine
Affects Version/s: 4.0 Beta 2
Fix Version/s: 4.0 Beta 2

QA Status: Covered by another test(s)
Test Details: See functional\gtcs\time_zone.fbt


 Description  « Hide
Today, 29th of March 2020, DST went into effect for my time zone (Europe/Amsterdam), using legacy time zone results in wrong values for TIME WITH TIME ZONE values.

For example

D:\DevSoft\FB_tests\Firebird-4.0.0.1820-0_x64>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect 'localhost:employee' user sysdba password 'masterkey';
Database: 'localhost:employee', User: SYSDBA
SQL> set bind of time with time zone to legacy;
SQL> select CURRENT_TIME, LOCALTIME from RDB$DATABASE;

 CURRENT_TIME LOCALTIME
============= =============
12:40:09.0000 13:40:09.0000

SQL>

I would expect that CURRENT_TIME and LOCALTIME to yield the exact same value in this case.

This incorrect behaviour only occurs on the date of the DST change: after changing my computer date to tomorrow (30th of March 2020), both had the same value.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 29/Mar/20 03:23 PM
DST started at 02:00 AM (going from 01:59:59 to 03:00) there?

Mark Rotteveel added a comment - 29/Mar/20 03:25 PM
Yes

Adriano dos Santos Fernandes added a comment - 29/Mar/20 03:34 PM
Ignoring SET BIND (it's not the problem here), do you think this is also problematic?

SQL> select cast(time '17:00:00 Europe/Amsterdam' as timestamp with time zone) from RDB$DATABASE;

                                                     CAST
=========================================================
2020-03-29 16:00:00.0000 Europe/Amsterdam

Mark Rotteveel added a comment - 29/Mar/20 03:52 PM
Yes, I also consider that problematic, the result should be 2020-03-29 17:00:00.0000 Europe/Amsterdam

Adriano dos Santos Fernandes added a comment - 29/Mar/20 05:14 PM
What about cast(time '02:10:00 Europe/Amsterdam' as timestamp with time zone)?

2020-03-29 02:10:00.0000 Europe/Amsterdam does not exist.

Adriano dos Santos Fernandes added a comment - 29/Mar/20 06:50 PM
Here is Oracle 18 results (session time zone is defined to Europe/Amsterdam):

SQL> select cast(time '03:05:00 Europe/Amsterdam' as timestamp with time zone) x from dual;
29-MAR-20 04.46.00.000000 AM EUROPE/AMSTERDAM

SQL> select cast(time '02:05:00 Europe/Amsterdam' as timestamp with time zone) x from dual;
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval

SQL> select cast(time '10:05:00 Europe/Amsterdam' as time) x from dual;
10.05.00 AM

Adriano dos Santos Fernandes added a comment - 29/Mar/20 08:40 PM - edited
That mentioned cases is not the major problem.

However we seems to have a major problem with TIME WITH TIME ZONE.

We define it in a way like TIMESTAMP WITH TIME ZONE, where the actual time is encoded in UTC (using the current date).

When we consider only fixed numeric time zones it doesn't matter, but with regions and DST that becomes a problem.

For example in your time zone it becomes impossible to have TIME '02:05:00 Europe/Amsterdam' or will result in weird times. Or arithmetics with it them in this date will bypass some times like it does in a TIMESTAMP WITH TIME ZONE.

I believe encoding of TIME WITH TIME ZONE should be changed so it stores the displayable hours/minutes in its int field instead of them encoded in UTC for the current date.

And that seems to make ISC_TIME_TZ_EX (but not ISC_TIMESTAMP_TZ_EX) obsolete, as ISC_TIME_TZ will have the necessary information to format it to string without ICU.

Edit: that would be switching a set of problem to another. Let me investigate more.

Adriano dos Santos Fernandes added a comment - 29/Mar/20 10:20 PM
I don't see any documentation about that, but in Oracle it treats TIME WITH TIME ZONE as being in date '0001-01-01'.

That explains this:

SQL> select cast(time '03:05:00 Europe/Amsterdam' as timestamp with time zone) x from dual;
29-MAR-20 04.46.00.000000 AM EUROPE/AMSTERDAM

See:

SQL> select extract(timezone_hour from time '03:05:00 Europe/Amsterdam') from dual;
0

SQL> select extract(timezone_minute from time '03:05:00 Europe/Amsterdam') from dual;
19

SQL> select extract(timezone_hour from timestamp '0001-01-01 03:05:00 Europe/Amsterdam') from dual;
0

SQL> select extract(timezone_minute from timestamp '0001-01-01 03:05:00 Europe/Amsterdam') from dual;
19

Mark Rotteveel added a comment - 30/Mar/20 09:02 AM
Given the time should be stored in UTC anyway, it should be possible to come to a single truth: use time in UTC, take current date in UTC, convert from UTC to specified zone.

Conversion from string or TIME (WITHOUT TIME ZONE) to TIME WITH TIME ZONE for times in 02:00 - 02:59 thing can be solved in one of two ways: 1) raise an error, or 2) be lenient and consider it a missed DST change and convert it to the equivalent in 03:00 - 03:59. However, I just realized the switch back to wintertime might be more problematic, when the clock goes back one hour. I wonder if the SQL standard has anything to say about this.

Adriano dos Santos Fernandes added a comment - 30/Mar/20 11:06 AM
> Given the time should be stored in UTC anyway, it should be possible to come to a single truth: use time in UTC, take current date in UTC, convert from UTC to specified zone.

It's not that simple, it what we have today and it has problems.

Also take into account indexes and unique constraints.

TIME currently may have different UTC value depending on when it has constructed, even considering a fixed time zone table.

> I wonder if the SQL standard has anything to say about this.

The standard does not have regions-based time zones, only fixed +/- offsets, so no problem happens there.

Sean Leyne added a comment - 30/Mar/20 02:19 PM - edited
Adriano,

I don't see how:

   SQL> select cast(time '03:05:00 Europe/Amsterdam' as timestamp with time zone) x from dual;
   29-MAR-20 04.46.00.000000 AM EUROPE/AMSTERDAM

is valid on Oracle. How can the minutes be different? (ignoring the Hour value for the moment)

Adriano dos Santos Fernandes added a comment - 30/Mar/20 02:57 PM
Sean, on 0001-01-01 Europe/Amsterdam has timezone 00:19 and today 02:00, so 1 hour and 41 minutes of difference in time zone.

Adriano dos Santos Fernandes added a comment - 30/Mar/20 02:59 PM
This is a very easy way to handle the problem in general, however this type of conversion from time to timestamp will appear incorrect (although it's not technically speaking).