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

Date used to convert TIME WITH TIME ZONE to GMT should be configurable [CORE6326] #6567

Open
firebird-automations opened this issue Jun 8, 2020 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Tony Whyman (twhyman)

A TIME WITH TIME ZONE data type records the time in GMT regardless of whether or not GMT or a local time is used to input the time value.

In order to translate a local time to GMT and back again, a date must be assumed so that the daylight savings time adjustment, if any, can be computed. In FB4 Beta 1, this was the CURRENT_DATE. In FB4 Beta 2, this has changed to 2020/1/1. Neither approach is perfect. This is particularly true when the time given is in the context of a specific date which is neither the current date, nor some arbitrary default.

It is proposed that while 2020/1/1 is as good a default as any, the date used to translate to and from GMT should be configurable i.e.

1. The server wide default date should be a configurable parameter in the firebird.conf file.

2. An SQL statement (e.g. SET TIME WITH TIME ZONE DATE TO '<date>'; ) should be provided to allow the user to change the default in the context of the current connection and/or transaction. This statement will need to be recognised and actioned by both the server (e.g. for time with time zone values given as a literal) and the client (for translating input and output time with time zone parameter values).

3.The date used to translate to and from GMT should be available as a column in the MON$ATTACHMENTS and the MON$TRANSACTIONS tables.

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

Does SQL standard require time with and without timezone to be convertible into each other at all? IMHO it would be better to deny such conversion.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Yes, the standard allows that.

TIME WITH TZ -> TIME WITHOUT TZ:
SV.UTC + http://SV.TZ (modulo 24)

TIME WITHOUT TZ -> TIME WITH TZ:
TV.UTC = SV - STZD (modulo 24);
http://TV.TZ = STZD

Where SV is the source value, TV is the target value, UTC is the UTC component of SV or TV (if and only if the source or target has time zone), TZ is the timezone displacement of SV or TV (if and only if the source or target has time zone), STZD is the SQL-session default time zone displacement.

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

If this is implemented, it should also be implemented using a DPB property to be able to specify it on connect and the base value that is reverted to when executing ALTER SESSION RESET.
I would also love to see an option that would apply the current date, and not just a fixed date.

@dmitry Sibiryakov, the problem isn't (only) with conversion between TIME WITH TIME ZONE and TIME WITHOUT TIME ZONE, the problem is fundamentally with the derivation of a TIME WITH TIME ZONE using a named zone given our use of UTC as the base of the WITH TIME ZONE types. You need to know the date to be able to derive the UTC time for CURRENT_TIME in the session zone, but also for CAST('12:00:00 Europe/Amsterdam' as TIME WITH TIME ZONE), and others (eg 12:00 Europe/Amsterdam is 11:00 UTC on 2020-01-01, but 10:00 UTC on 2020-06-01).

Personally, I would feel more comfortable with a setting that would allow you to use a named session zone for configuration only, and the server would use that to derive offset time zones using the rules of the named session zone, where it would apply a current-date rule instead of a fixed date.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

This feature request does not make any sense to me. The only thing it says correctly is that the old and current behavior are not perfect, which are well know.

But to fix the thing wants a problematic approach (with index, comparations, date with time gaps, etc) that does not make any sense to be configurable.

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

1 participant