Issue Details (XML | Word | Printable)

Key: CORE-6326
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Tony Whyman
Votes: 0
Watchers: 6

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

Date used to convert TIME WITH TIME ZONE to GMT should be configurable

Created: 08/Jun/20 11:18 AM   Updated: 08/Jun/20 01:24 PM
Component/s: Engine
Affects Version/s: 4.0 Beta 2
Fix Version/s: None

Environment: All

QA Status: No test

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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 08/Jun/20 11:28 AM
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.

Dmitry Yemanov added a comment - 08/Jun/20 11:39 AM
Yes, the standard allows that.

  SV.UTC + SV.TZ (modulo 24)

  TV.UTC = SV - STZD (modulo 24);

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.

Mark Rotteveel added a comment - 08/Jun/20 12:44 PM - edited
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.

Adriano dos Santos Fernandes added a comment - 08/Jun/20 01:24 PM
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.