Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
On 06.04.2020 12:10, Mark Rotteveel wrote: On 2020-04-05 21:49, Adriano dos Santos Fernandes wrote: On 05/04/2020 12:40, Mark Rotteveel wrote: If we do decide to follow the standard in this regard, then I would recommend that we make an exception for the conversion applied by the bind of TIME ZONE (or a specific time zone type) to LEGACY to convert using the session time zone. SET BIND just changes automatic described types. Types used by client messages (which generally but not always comes from the described types) works likes standard variables. So no, that would be a complete hack. And yes, this may be problematic for bind of TZ to WITHOUT-TZ types, although there are the extended types which makes this less worse. The extended types are not a solution for clients that need to use a legacy binding to function correctly. With legacy bind, clients that don't support the new types clientside can still work with the data. If we are effectively going to truncate time zone information, that means that clients with legacy bind will get inconsistent (and, in my opinion, unusable) information when accessing a database that has times in different zones or offsets (because 2020-04-06 01:00:00+02:00 and 2020-04-06 01:00:00+04:00 would both be transformed to 2020-04-06 01:00:00, making the values meaningless). The current conversion from/to the session time zone at least allows a coherent view of the data (effectively normalized to a single zone), with a session zone of Europe/Amsterdam the previous times would be transformed to 2020-04-06 01:00:00 and 2020-04-05 23:00:00, which makes sense. I 100% agree with Adriano that making rules for server/client transfers differ from the rest places is bad-bad-bad. Luckily looks like we have no voices suggesting to follow standard exactly in this aspect i.e. all problem with having an exception for SET BIND appears meaningless. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
On 2020-04-05 21:49, Adriano dos Santos Fernandes wrote: On 05/04/2020 12:40, Mark Rotteveel wrote: If we do decide to follow the standard in this regard, then I would recommend that we make an exception for the conversion applied by the bind of TIME ZONE (or a specific time zone type) to LEGACY to convert using the session time zone. SET BIND just changes automatic described types. Types used by client messages (which generally but not always comes from the described types) works likes standard variables. So no, that would be a complete hack. And yes, this may be problematic for bind of TZ to WITHOUT-TZ types, although there are the extended types which makes this less worse. The extended types are not a solution for clients that need to use a legacy binding to function correctly. With legacy bind, clients that don't support the new types clientside can still work with the data. If we are effectively going to truncate time zone information, that means that clients with legacy bind will get inconsistent (and, in my opinion, unusable) information when accessing a database that has times in different zones or offsets (because 2020-04-06 01:00:00+02:00 and 2020-04-06 01:00:00+04:00 would both be transformed to 2020-04-06 01:00:00, making the values meaningless). The current conversion from/to the session time zone at least allows a coherent view of the data (effectively normalized to a single zone), with a session zone of Europe/Amsterdam the previous times would be transformed to 2020-04-06 01:00:00 and 2020-04-05 23:00:00, which makes sense. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
05.04.2020 18:21, Adriano dos Santos Fernandes wrote: I also agree that the current behavior is much better. Me too. Let's keep it. On the other hand, we have others weird standard behaviors (CASE with string literals producing CHAR result, for example). Maybe we regret to did it in the standard way and would create it in a better way if it's doing today? Yes, I think so. Moreover, I see no problems to accept our mistake and re-work it to be more suitable for the real life. At least in the major release (v4 or the next one). And BTW, speaking about the make_desc_from_list() logic, IMO it was a big mistake (maybe ours, maybe standard's) to use it for *both* function argument lists (CASE/COALESCE/etc) and select-lists (UNION). I'm pretty sure this is wrong in the latter case. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
On 05/04/2020 12:40, Mark Rotteveel wrote: > > If we do decide to follow the standard in this regard, then I would > recommend that we make an exception for the conversion applied by the > bind of TIME ZONE (or a specific time zone type) to LEGACY to convert > using the session time zone. > SET BIND just changes automatic described types. Types used by client messages (which generally but not always comes from the described types) works likes standard variables. So no, that would be a complete hack. And yes, this may be problematic for bind of TZ to WITHOUT-TZ types, although there are the extended types which makes this less worse. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
On 05-04-2020 17:21, Adriano dos Santos Fernandes wrote: On 05/04/2020 05:22, Vlad Khorsun wrote: 05.04.2020 9:46, Mark Rotteveel wrote: Although I'm all for following the standard, I wonder of making this change is the right one to do. The current behaviour makes a lot of sense to me, and I'm not sure if the behaviour defined by the standard will make things understandable for users. I'm agree with Mark I also agree that the current behavior is much better. On the other hand, we have others weird standard behaviors (CASE with string literals producing CHAR result, for example). Maybe we regret to did it in the standard way and would create it in a better way if it's doing today? So I do not have a problem either way we decide. Would like to know Dmitry opinion. The problem we resolve with current solution is resolved in the standard way with cast(value at local as timestamp). If we do decide to follow the standard in this regard, then I would recommend that we make an exception for the conversion applied by the bind of TIME ZONE (or a specific time zone type) to LEGACY to convert using the session time zone. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
On 05/04/2020 05:22, Vlad Khorsun wrote: > 05.04.2020 9:46, Mark Rotteveel wrote: >> Although I'm all for following the standard, I wonder of making this >> change is the right one to do. >> >> The current behaviour makes a lot of sense to me, and I'm not sure if >> the behaviour defined by the standard will make things understandable >> for users. > > I'm agree with Mark > I also agree that the current behavior is much better. On the other hand, we have others weird standard behaviors (CASE with string literals producing CHAR result, for example). Maybe we regret to did it in the standard way and would create it in a better way if it's doing today? So I do not have a problem either way we decide. Would like to know Dmitry opinion. The problem we resolve with current solution is resolved in the standard way with cast(value at local as timestamp). Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
On 05/04/2020 07:46, Mark Rotteveel wrote: Although I'm all for following the standard, I wonder of making this change is the right one to do. The current behaviour makes a lot of sense to me, and I'm not sure if the behaviour defined by the standard will make things understandable for users. The main problem with much of this is that the 'standard' does not seem to actually understand the whole problem. MUCH of what is being discussed is the addition and subtraction of a fixed time offset. Much as happens in the browser headers currently. It has NOTHING to do with carrying a valid TIMEZONE around with the data and being able to handle simple local timezone time calculations which cross DST and other transitions relating to the full timezone rule set ... the second that a timezone identifier is dropped then one has lost vital information! ( The fact that the TZ database also drops validated rule changes prior to 1970 is another critical element here ... ) -- Lester Caine - G8HFL - Contact - https://lsces.uk/wiki/Contact L.S.Caine Electronic Services - https://lsces.uk Model Engineers Digital Workshop - https://medw.uk Rainbow Digital Media - https://rainbowdigitalmedia.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
05.04.2020 9:46, Mark Rotteveel wrote: Although I'm all for following the standard, I wonder of making this change is the right one to do. The current behaviour makes a lot of sense to me, and I'm not sure if the behaviour defined by the standard will make things understandable for users. I'm agree with Mark Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6276) Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone
Although I'm all for following the standard, I wonder of making this change is the right one to do. The current behaviour makes a lot of sense to me, and I'm not sure if the behaviour defined by the standard will make things understandable for users. Mark On 05-04-2020 02:07, Adriano dos Santos Fernandes (JIRA) wrote: Conversion from WITH TIME ZONE to WITHOUT TIME ZONE types should drop the time zone instead of use the session time zone Key: CORE-6276 URL: http://tracker.firebirdsql.org/browse/CORE-6276 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 4.0 Beta 1 Reporter: Adriano dos Santos Fernandes Firebird (as well PostgreSQL) uses the session time zone when converting from WITH-TZ to WITHOUT-TZ types. Example: set time zone '-03:00'; SQL> select cast(timestamp '2020-04-04 10:00:00 -05:00' as timestamp) from rdb$database; CAST = 2020-04-04 12:00:00. When converting it back to TIMESTAMP WITH-TZ (not altering the session time zone), we get an equal (equivalent) timestamp (which different offset): SQL> select cast(timestamp '2020-04-04 12:00:00.' as timestamp with time zone) from rdb$database; CAST = 2020-04-04 12:00:00. -03:00 SQL> select timestamp '2020-04-04 12:00:00. -03:00' = timestamp '2020-04-04 10:00:00 -05:00' from rdb$database; === However the standard SQL says that conversion from TIMESTAMP WITH-TZ to TIMESTAMP WITHOUT-TZ should be "SV.UTC + SV.TZ" (SV is the source value). In practice this means to drop the time zone. Oracle implementation also respects (and documents in https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm) this behavior: "Insert the same date and time as a TIMESTAMP WITH TIME ZONE literal. Oracle converts it to a TIMESTAMP value, which means that the time zone information is dropped.". Also accordingly to the standard the conversion from WITHOUT-TZ to WITH-TZ is "TV.UTC = SV - STZD; TV.TZ = STZD" (TV is target value; STZD is the session time zone). So in this respect we are correct and then converting WITH-TZ -> WITHOUT-TZ -> WITH-TZ does not necessarily produces an equivalent value. -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
