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

Bind time with time zone to legacy produces wrong values [CORE6271] #6513

Closed
firebird-automations opened this issue Mar 29, 2020 · 21 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

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.

Commits: f6cda2a FirebirdSQL/fbt-repository@3870320

====== Test Details ======

See functional\gtcs\time_zone.fbt

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: Today, 29-03-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 (30-03-2020), both had the same value.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

DST started at 02:00 AM (going from 01:59:59 to 03:00) there?

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Yes

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Yes, I also consider that problematic, the result should be 2020-03-29 17:00:00.0000 Europe/Amsterdam

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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)

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Covered by another test(s)

Test Details: See functional\gtcs\time_zone.fbt

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 RC 1 [ 10930 ]

Fix Version: 4.0 Beta 2 [ 10888 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 Beta 2 [ 10888 ]

Fix Version: 4.0 RC 1 [ 10930 ] =>

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