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
Comments
Modified by: @mrotteveelComponent: Engine [ 10000 ] |
Modified by: @mrotteveeldescription: 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 CURRENT_TIME LOCALTIME 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 CURRENT_TIME LOCALTIME 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. |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @asfernandes DST started at 02:00 AM (going from 01:59:59 to 03:00) there? |
Commented by: @mrotteveel Yes |
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;
========================================================= |
Commented by: @mrotteveel Yes, I also consider that problematic, the result should be 2020-03-29 17:00:00.0000 Europe/Amsterdam |
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. |
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; SQL> select cast(time '02:05:00 Europe/Amsterdam' as timestamp with time zone) x from dual; SQL> select cast(time '10:05:00 Europe/Amsterdam' as time) x from dual; |
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. |
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; See: SQL> select extract(timezone_hour from time '03:05:00 Europe/Amsterdam') from dual; SQL> select extract(timezone_minute from time '03:05:00 Europe/Amsterdam') from dual; SQL> select extract(timezone_hour from timestamp '0001-01-01 03:05:00 Europe/Amsterdam') from dual; SQL> select extract(timezone_minute from timestamp '0001-01-01 03:05:00 Europe/Amsterdam') from dual; |
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. |
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. |
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; is valid on Oracle. How can the minutes be different? (ignoring the Hour value for the moment) |
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. |
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). |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 Beta 2 [ 10888 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Covered by another test(s) Test Details: See functional\gtcs\time_zone.fbt |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: