Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 12:58, Mark Rotteveel wrote: That is what you can expect when linking to documentation on a branch called 'work' (which, afaik, was deleted a very long time ago), which implies it's transient. It had been used in relation to a previous thread and was the only link I could see earlier ... and I have corrected that ... -- 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] Value of CURRENT_TIME is not current time
On 2020-06-03 12:51, Lester Caine wrote: On 03/06/2020 09:24, Alex Peshkoff via Firebird-devel wrote: Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? It's stable but it will always reference that file in a state reflecting current development state. Really stable URL will arrive after creation of B4_0_Release branch. Problem was that the first link no longer exists and github then gets a bit more difficult to navigate ... Linking to github in discussions does not always produce the same document even ignoring the fact that these documents will be evolving, so it is important to have a set of non-github documentation sooner rather than later. That is what you can expect when linking to documentation on a branch called 'work' (which, afaik, was deleted a very long time ago), which implies it's transient. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 2020-06-02 23:32, Adriano dos Santos Fernandes wrote: On 02/06/2020 16:45, Mark Rotteveel wrote: Documentation should be improved for this case too. This essentially means that select * from atable where sometimecolumn > current_time; will yield different results when the time zone is 'Europe/Amsterdam' than it does when the time zone is UTC. I think that is extremely confusing, and not acceptable. Test case showing the problem (with time/tz constant), please. For constants we can explain this away with the fact that 'it is the time on 2020-01-01', that logic doesn't really work well with something that is purported to be the current time, and is defined as based on UTC. I'm specifically talking about the fact that given TIME WITH TIME ZONE is defined as time in UTC with additional information (either offset or a named zone) to derive a local time, that CURRENT_TIME in fact does not conform to that definition, because it is in fact not UTC. Right now in Europe/Amsterdam it is 13:37 (11:37 UTC). If I use CURRENT_TIME right now, I will get a time of 12:37 UTC (which is intended to render as 13:37), which is not correct. Especially not if you consider the fact that the mere changing of the time zone using SET TIME ZONE 'UTC' would result in a time of 11:37 UTC. In essence, I think this makes named zones largely meaningless for TIME WITH TIME ZONE. If we can't fix this somehow, then I at least want an option (DPB-item/SET xxx) where even with a named session time zone, CURRENT_TIME and CURRENT_TIMESTAMP and casts of literals to TIME WITH TIME ZONE will yield offset-based times (derived from the named zone rules for 'right now'). Otherwise I see no other option than to make Jaybird default to use UTC or the offset at connect time as the session time zone for sake of correctness. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 09:24, Alex Peshkoff via Firebird-devel wrote: Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? It's stable but it will always reference that file in a state reflecting current development state. Really stable URL will arrive after creation of B4_0_Release branch. Problem was that the first link no longer exists and github then gets a bit more difficult to navigate ... Linking to github in discussions does not always produce the same document even ignoring the fact that these documents will be evolving, so it is important to have a set of non-github documentation sooner rather than later. -- 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] Value of CURRENT_TIME is not current time
On 03/06/2020 04:10, Alex Peshkoff via Firebird-devel wrote: > On 2020-06-03 00:17, Dimitry Sibiryakov wrote: >> 02.06.2020 21:45, Mark Rotteveel wrote: >>> I think that is extremely confusing, and not acceptable. >> >> That's why everyone was given several years to replace CURRENT_TIME >> with LOCALTIME and don't use TIME WITH TIMEZONE at all. >> > > Yes. What we definitely need in RN: "Think twice before using TIME > WITH TIMEZONE and better don't use it at all". > I'd say the problem is with conversions from/to timestamps, from/to different zones and comparisons of values using different zones - only when using named zones. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 2020-06-03 10:56, Lester Caine wrote: On 03/06/2020 08:23, Lester Caine wrote: On 02/06/2020 22:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? It's stable but it will always reference that file in a state reflecting current development state. Really stable URL will arrive after creation of B4_0_Release branch. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 03/06/2020 08:23, Lester Caine wrote: On 02/06/2020 22:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md should be a stable URL ? Certainly I have no intention of doing anything other than retaining ALL timestamps at UTC AND running the server local time as UTC. Displaying and handling CLIENT local times is an interface problem and not something which should be inconsistent in the core data ... especially when one does not know what future time offsets will actually be ... It would be nice to see the final paragraph much closer to the top so that the fundamental problems of using the TZ database is highlighted earlier. THAT is an explanation of where the FB4 approach to them may or may not be appropriate. Also still missing is the lack of second accuracy with timezone offsets in the FB4 version and that dates prior to 1970 may produce different results. It should be clear exactly which version of TZ data is bundled so we know if pre-19790 data is included or not ... -- 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] Value of CURRENT_TIME is not current time
On 02/06/2020 22:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Has https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Been replaced with something more up to date? Certainly I have no intention of doing anything other than retaining ALL timestamps at UTC AND running the server local time as UTC. Displaying and handling CLIENT local times is an interface problem and not something which should be inconsistent in the core data ... especially when one does not know what future time offsets will actually be ... -- 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] Value of CURRENT_TIME is not current time
On 2020-06-03 00:17, Dimitry Sibiryakov wrote: 02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. Yes. What we definitely need in RN: "Think twice before using TIME WITH TIMEZONE and better don't use it at all". Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 02/06/2020 16:45, Mark Rotteveel wrote: > On 02-06-2020 21:34, Adriano dos Santos Fernandes wrote: >> On 02/06/2020 16:11, Mark Rotteveel wrote: >>> Maybe it is better if CURRENT_TIME doesn't use the named zone, but >>> instead always uses an offset based value. >>> >> I'm not sure it is. This would create other inconsistencies >> (cast(current_timestamp as time with time zone) <> current_time). >> >> There is no problem with a time with zone, the problem is when one uses >> it to do things that depends on a date, like you did here. >> Your solution does not fix any problem, as current_time is not the singlel way to create times/tz. >> Documentation should be improved for this case too. > > This essentially means that > > select * from atable where sometimecolumn > current_time; > > will yield different results when the time zone is 'Europe/Amsterdam' > than it does when the time zone is UTC. I think that is extremely > confusing, and not acceptable. > Test case showing the problem (with time/tz constant), please. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
02.06.2020 21:45, Mark Rotteveel wrote: I think that is extremely confusing, and not acceptable. That's why everyone was given several years to replace CURRENT_TIME with LOCALTIME and don't use TIME WITH TIMEZONE at all. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 02-06-2020 21:34, Adriano dos Santos Fernandes wrote: On 02/06/2020 16:11, Mark Rotteveel wrote: Maybe it is better if CURRENT_TIME doesn't use the named zone, but instead always uses an offset based value. I'm not sure it is. This would create other inconsistencies (cast(current_timestamp as time with time zone) <> current_time). There is no problem with a time with zone, the problem is when one uses it to do things that depends on a date, like you did here. Documentation should be improved for this case too. This essentially means that select * from atable where sometimecolumn > current_time; will yield different results when the time zone is 'Europe/Amsterdam' than it does when the time zone is UTC. I think that is extremely confusing, and not acceptable. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Value of CURRENT_TIME is not current time
On 02/06/2020 16:11, Mark Rotteveel wrote: > The recent changes to fix the derivation of TIME WITH TIME ZONE to > 2020-01-01 has weird consequences. In summer time, the value of for > example CURRENT_TIME is now off by 1 hour when looking at the UTC time > value. > > This leads to the following confusing results: > > SQL> set time zone 'Europe/Amsterdam'; > SQL> select current_time at time zone 'UTC' from rdb$database; > > AT > == > 20:08:36. UTC > > SQL> set time zone 'UTC'; > SQL> select current_time at time zone 'UTC' from rdb$database; > > AT > == > 19:08:45. UTC > > SQL> select current_time from rdb$database; > > CURRENT_TIME > == > 19:08:52. UTC > > SQL> > > Maybe it is better if CURRENT_TIME doesn't use the named zone, but > instead always uses an offset based value. > I'm not sure it is. This would create other inconsistencies (cast(current_timestamp as time with time zone) <> current_time). There is no problem with a time with zone, the problem is when one uses it to do things that depends on a date, like you did here. Documentation should be improved for this case too. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel