Re: [Firebird-devel] Timezone IDs in Firebird
On 14/12/2020 10:49, Jiří Činčura wrote: can the current timezone IDs in FB4 and further be considered stable? I.e. "Asia/Krasnoyarsk" always <=> 65247. Give or take: New timezones added only to the end of the list and removed timezones will leave a gap in the IDs. There are currently discussions on the TZ list on changing the name of some timezone ID's, but old ID's are not removed simply because they remain in use in historic data. We are still battling the problem of pre-1970 timezone data being missing from many 'default' installations where ID's simply select a data set that matches post 1970 rather than one that actually matches the fully validated pre-1970 data so again some timezone ID's simply return poor information where their being removed would be a safer approach ... the whole system is still an unreliable mess! -- 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] Crazy about time/timestamp with timezone
On 09/07/2020 08:40, Pavel Cisar wrote: Dne 08. 07. 20 v 21:13 Mark Rotteveel napsal(a): On 08-07-2020 20:16, Pavel Cisar wrote: As I said, CET is a zone that has a DST rule, that means that - for example - on 2020-01-01, 12:30 CET is 11:30 UTC, while at 2020-06-02, 12:30 CET (== 12:30 CEST) is 10:30 UTC. CET is NOT a zone with DTS rule, CET (Central European Time) is 1 hour ahead of Coordinated Universal Time (UTC). This time zone is in use during standard time in: Europe, Africa. Some places observe daylight saving time/summer time during the summer, and therefore use CEST (Central European Summer Time) in the summer. So, timezones like 'Europe/Prague' HAVE DST rules where they switch between CET and CEST. For example Algeria and Tunisia have CET the whole year as they don't switch to CEST. And that is yet another reason not to use the short time zone ids, because they are ambiguous, the ICU time zone database does apply DST rules to CET: How ICU can apply DTS rules on CET? CET has NO DTS rules, only named zones (countries) can. CET is simply time that is UTC+01:00, period. So if ICU (and through it Firebird) defines DTS for CET, it's plain wrong. If it wouled be true and CET would have DTS rules, Algeria and Tunisia couldn't have it for WHOLE YEAR! However, the impression that CET has DTS rules could happen when library or system does use the same name for DTS shifts, i.e. uses only CET and does not recognize CET/CEST. In such case the UTC offset may differ while the name is the same. Other systems use different names to signal that offset differs (because DTS is actually a seasonal shift to another timezone). Both approaches (single name, different offset + distinct names for offsets) are widely used and acceptable. The single name + different offset approach is actually NEWER than multi-name and is still not the dominant or most significant. For example the POSIX systems (i.e. Linux) today use the multiname approach (and they also use the IANA timezone database). Please, read some documentation about the problem in general, not only documentation for libraries you (or Firebird) use to get unbiased view. And yes, it's a mess, but we don't live in ideal world. Much of this goes back to the bodge that browsers use in only providing a clients offset. Finally getting around to fixing that particular bug would go a long way to making things work better. NEEDING to have clients log into a services in order that you can establish their correct DST rules has been essential for many years. This is why I prefer to currently manage local times based on an independent field identifying the correct local location. -- 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] Crazy about time/timestamp with timezone
On 08/07/2020 14:44, Dimitry Sibiryakov wrote: 08.07.2020 15:32, Adriano dos Santos Fernandes wrote: I personally think TIME-TZ with regions are a valid thing (albeit weird depending on the operations) because it fills a gap where one creates a TIME and a additional region column. TIME-TZ with offsets only (no regions) does not have the weird things by definition, but weird things will happen when converting from timestamps. SQL 2003 standard supports only offsets in TIME WITH TIME ZONE, not region names. And the standard has been wrong since it was first published ... it ignored DST management simply because it was too difficult to include. -- 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] ODP: Modern C++: constexpr
On 17/06/2020 10:13, Dimitry Sibiryakov wrote: 17.06.2020 02:57, Leyne, Sean wrote: I don't see why we shouldn't be doing the same. Because we have no resources and time to waste to it...? Same discussion is going on on many lists but the general consensus seems to be to wait until some of the higher level facilities decide on a new language before doing anything. github will be changing and there is no point trying to push something different to their 'dictates' even if there is no complete agreement on what 'it' decides. -- 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 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 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 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] ICU File Deployment Guidance
On 29/04/2020 13:16, Tony Whyman wrote: The most recent Debian version is 2020a-1 (released 4 days ago). so I'll be interested to see how quickly that feeds down to ubuntu 18.04 - if ever. Its changelog is 2020a is the first release of TZ for 2020 and in addition to the Morocco and Yukon offset changes it also includes a change to timezone identifiers so America/Godthab has been renamed to America/Nuuk There were only 3 releases in 2019 which is a little strange, with 2019c being released last December, but the changes spread over the period and their actual corrections can be found in the development repository https://github.com/eggert/tz -- 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] ICU File Deployment Guidance
On 29/04/2020 12:37, Paul Reeves wrote: - The changes will, in all probability, more than 99% of the time, have no relevance for over 99% of our users. But can one take the risk of ignoring them? Unfortunately this 'excuse' is quite prevalent in most actions relating to TZ ... How do I ensure that data I distribute which includes World War Two time offsets will be handled correctly if the user does not have 'backzone' on their setup. It's too difficult so handle pre-1970 data so we will ignore it! The vast majority of users will never know ... - Even if we can provide timely, automated updates how will a server installation know to look for them? A task scheduler of some sort would need to check. We don't have one built in to Firebird so it would mean adding something platform specific. This part of my objection to even having wasted the time to include a restricted support for timezones AT ALL in FB4. The starting point of solving the problem of handling potentially critical changes to TZ data is tzdist, but while the RFC has been approved, no one has stepped up to the plate to provide a feed. The whole point of tzdist is that you can ask if a rule set has changed since it was last used ( or even since the data was normalized ) and one can at least establish if the event you published for say next week will be at the same UTC time if astronomical observations have change the start of Ramadan for example. tzdist also provides validation of the time frame over which a rule set is valid so one can flag when there is no offset available for a pre1970 date. This is not a firebird problem ... this is a problem with the core data, and TZ is only required to provide post 1970 data. There is no source of pre 1970 data that can be relied on despite the fact that a growing historic record is being validated even in the TZ backzone data. SO TODAY ... the best way of working is using UTC normalized data along with additional fields recording location and version and type of tz data used to provide that normalization! The FB4 'timezone' is yet another unreliable data source! And this is the same for historic data and published calendars for the coming 6 months ... -- 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] Installing the ICU Files under Windows
On 28/04/2020 15:10, Kjell Rilbe wrote: https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md#updating-the-time-zone-database As a Firebird user I would suggest that this article/info is clearly stated in every version of Firebird release notes, and also that it's pointed out in the installation guide. I'm sure many users won't be aware of this otherwise. (My ecuses if this is already the case, but I at least haven't taken notice if it is). And keep it updated with the recent changes to identifiers ... It still lacks a warning about not using this with pre 1970 dates -- 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
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] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
On 06/03/2020 09:43, Mark Rotteveel wrote: But that explanation primarily seems to boil down to that Adriano didn't like it and that you didn't want to discuss it any further. In any case, we seem to have a stalemate here, so I guess there is not much point in continuing to argue. I'll grudgingly implement support in Jaybird by handling it identical to a normal TIMESTAMP WITH TIME ZONE, just in case someone configures a bind to EXTENDED TIME(STAMP) WITH TIME ZONE. Or simply disable access to any of this already faulty TIME ZONE stuff. Until it supports second accuracy as provided by the TZ database it is already broken ... At least the documentation should point out the limitations of the crude shortcut that as been adopted ... -- 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] Web site cert issue
On 14/02/2020 12:28, Dimitry Sibiryakov wrote: What on this site requires encryption is still an open question. Being forced to add certificates to all my client sites simply to stop google and browsers complaining that they are 'insecure' I agree with your comment on sites full of open data, but things have progressed to a point where NOT using HTTPS is virtually impossible without other agro. That said, letsencrypt can't be relied on to complete a renewal cycle ... I've just had manually clean up some certs myself so you have to keep on top of every one :( -- 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] TIME WITH TZ
On 05/09/2019 08:52, Alex Peshkoff via Firebird-devel wrote: Error is error, doesn't matter where it happens. Or you could offer "fallback" for wrong database handle\wrong server name in isc_attach_database\not working client DNS\etc cases ? Looks like finally you agreed that return a different tz value is like dropping a different database because engine could not drop the asked one, and no workaround should be inserted replacing region codes by wrong offsets. Why are you so sure that offset will be invalid? What's incorrect in replacing TZ 'MSK' with offset +3? Has anybody actually documented just what the problem is? While supplying an 'offset' is fine for any single timestamp, TZ supplies a lot more information than simply the current time offset, it also supplies the DST rules and THIS is the area that is not being handled well by any of the current TZ offerings. That is where a TZ identifier to go with the time is important and the simple 'offset' has never worked. -- 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] ODP: Firebird 4: Could not find acceptable ICUlibrary
On 13/07/2019 08:09, Mark Rotteveel wrote: 2. Changing how WITH TIME ZONE types fundamentally work in the protocol this late in the development cycle is in my opinion problematic, and the alternatives suggested up to this point are - in my opinion - not better and in some cases worse. The current implementation is something I will never use so I will probably not even bother switching TO FB4 ... The whole management of timezones is still broken and until there is a fully functional database as a base to build on it would be MUCH better not to add to the mess by yet another hack that fixes very little but adds more things to be patched by real applications ... Timezone data has a one second accuracy and MANY edge cases will hit that so anything built in must be able to process that data! Simply pull it all until a full solution can be addressed! -- 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] ODP: Firebird 4: Could not find acceptable ICUlibrary
On 14/06/2019 16:22, Mark Rotteveel wrote: While implementing this feature in Jaybird I even considered using the approach used by the PostgreSQL JDBC and that was simply always using the UTC time and not even bothering with the offset and region information. PostgreSQL's built in timezone handling just does not work anyway! The key factor here is that the ONLY reason for using timezones is to provide a world agnostic time base. Firebird works PERFECTLY in this world if the bog standard server installation is running on a UTC based clock. The CLIENT displays IT'S local time or the time at an alternate location. It is the location identifier that returns the correct set of rules for the time you want to DISPLAY. And that may or may not originate from the database. Eliminating any errors convert BACK to UTC when the rule set may not be cleanly identified. This was time zone 101 20+ years ago and nothing has changed since ... if you ONLY want local time then just use that instead of UTC and ignore the DST overlap problems. -- 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] ODP: Firebird 4: Could not find acceptable ICUlibrary
On 14/06/2019 14:27, Adriano dos Santos Fernandes wrote: Note that ICU is a theoretical implementation detail, so Firebird has a gateway for it also in the client to not reimplement it. But this is important: at least currently, ICU is an implementation detail. Currently if Firebird wants to implement some time zone not present in ICU, it can. Not saying it should. My own problem is the the use of The smaller UK timezone ID's ... Isle of Man, Guernsey and Jersey ... which on most systems are only accurate post 1970. Although ICU does not currently identify if that is the case. World War 2 archival material can not reliably use the rules supplied because different systems may not have the proper pre-1970 data :( We do not actually have any indication if the rule set is valid pre-1970. This is a basic implementation detail for any system trying to use TZ data :( -- 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] ODP: Firebird 4: Could not find acceptable ICUlibrary
On 11/06/2019 22:46, Vlad Khorsun wrote: The simplest way would be to drop IDs and deliver to client only bias. It is enough for all usages of timestamp with timezone I can think about. I already offer to deliver to client both ID and displacement (offset, bias)... It costs nothing as current 10 bytes in wire protocol anyway padded up to 12 bytes. In which case restoring seconds accuracy need for LMT management should be possible, but certainly the rule set being used IS essential since the bias has no indication as to DST situation ... It's worth pointing out that this same discussion is in part being had about the Java timezone handling ... there is no consensus on how it should be done! -- 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] ODP: Firebird 4: Could not find acceptable ICUlibrary
On 11/06/2019 17:25, Vlad Khorsun wrote: Also, don't forget to update ICU data file on all clients. TZ database is changed often enough. For example, in 2018 there was 7 versions, see at https://github.com/unicode-org/icu-data/tree/master/tzdata/icunew Actually there were 9 releases ... https://data.iana.org/time-zones/ and the current development release is at https://github.com/eggert/tz which has tz changes that have not yet been publicly released. -- 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] Firebird 4: Could not find acceptable ICU library
On 11/06/2019 12:32, Vlad Khorsun wrote: One of "simple" solutions could be to ask sever once for whole conversion table and to keep it at some temp file (or nearby fbclient\app) for future usage. It is also not ideal, as different servers could have different ICU versions, but it could be a starting point to think on the better solution. So, actially, we need not an "id to name" conversion table. We need whole time zone database. It is a "bit" more complex task. It is even more complex that that ... The IDENTIFIERS used in the TZ database are purely that. Identifiers. And the guide lines for using TZ data points specifically to using something other than those identifiers when supplying displays in client applications. On top of this, the current rule set being used by the operating system may well not match the rules used previously to when processing the data, so we need access to ALL the historic changes to the rules or at least a copy of the rules used to store data and the current rules with some mechanism to update that data ... -- 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] Invalid and ambiguous timestamps with time zones
On 24/04/2019 14:44, Adriano dos Santos Fernandes wrote: Data should be validated/fixed when new rules are installed. So IMO the problem is letting OS (or some shared component) be the master of time zones rules instead of Firebird engine having its own controlled table. Exactly ... And the fact that TZ has been dragging it's feet even to provide version numbers for each update does not help things! The key element that needs to be handled is the fact that there HAS been a change while a device was asleep for whatever reason. But having to go though a large volume of normalised data is not the way to fix it either. One needs to be able to read a record and know if it's valid against current rules ... and with the current drive to eliminate DST in parts of the world, those records could well be a current set of diary entries! PRACTICALLY given that normalization is such a minefield, it still makes sense to store a local time and location rather than trying to make UTC/TZ work. We use UTC for real time activities such as log file time stamps against a UTC based clock. Displaying these in local time is often academic and would never store a TZ flag with them as it's the client local time you want not the events local time. While you want to be able to compare the time of events in different timezones, the FB4 timezone data type is the last thing one would use to do it! Much of the data you will want to compare against will be from other sources, and the lack of a reliable system of identifying what rules were used to create that data is the problem tzdist was intended to at least help with. So for example, when one gets off the flight and your tablet checks it's diary you can see that perhaps the meeting you are attending has now changed time due to a short notice change to local DST ... and as has happened in recent years the meeting may already have started an hour early! What IS required is a database with identified rule sets and change records relating to those so one can take a normalized time, identify that the normalization is now suspect, so you need the original local time using the recorded rule used, and then a new UTC time based on probably the new rule set ... without access to the version of the rule set used you do not know if the normalized time has changed or not ... and the OS simply updating the rules without any warning causes all sorts of problems? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Invalid and ambiguous timestamps with time zones
On 23/04/2019 19:10, Adriano dos Santos Fernandes wrote: Regarding the exception, what would happen if you store valid date at that time and you later, with new DST rules, try to read it? That is not a problem. The ambiguity exists only when transforming a timestamp+TZ (as one sees it) to UTC. After parse the literal, it's always in UTC+TZ. UTC+TZ is always transformed to a valid displayable timestamp+TZ. But whether it is the RIGHT transformation is any bodies guess! We still need a properly managed solution to handing local time normalization, and since even the TZ database does not provide that much of it is always going to be guesswork. :( The SAFE option for handling normalized times is to record the location rather than some arbitrary timezone. The second one selects a timezone one ALSO needs to record the version of timezone data used with it ... timestamp+TZ is simply not the solution for most cases - current day or historic ... and the further restrictions FB4 introduces just makes matters worse! -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [PHP-DEV] [RFC] Unbundle ext/interbase
On 22/03/2019 13:26, Kalle Sommer Nielsen wrote: G'day internals I'd like to start the discussion for the future of the ext/interbase extension: https://wiki.php.net/rfc/deprecate-and-remove-ext-interbase The rationale for pushing this extension out of the core is mentioned in the RFC. Unless there is any serious issues raised here, then I will put this into voting on Monday 8th of April, noon EET (which is a good two and a half weeks away). The intended voting period is set for 2 weeks, meaning if voting proceeds, the pools will be closed around Monday 22th of April, noon EET. It is not that we don't want to stand up and maintain it, it has been impossible in recent years to get a handle on just what needs to be done TO maintain it. The PDO extension is in a much worse state than the main interbase one but both of them do their jobs as well as they can given that in the case of PDO it can't handle the cross database transactions, something that the main extension does quite happily. Personally I've been wasting time recently trying to keep alive sites that are using MySQL and the main problem with MySQL is the one thing that Firebird does nicely. Backup just runs as a secondary cron job independent of PHP while MySQL is reliant on PHP and current backups on some wordpress powered sites are failing because they run out of time or memory. I've never had a problem with loosing data with Firebird while I've had recover MySQL situations a few times in the last year! So all we are asking for is HELP with the code changes that result from changes to the PHP API to keep this available. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Timezone id in big endian clients?
On 04/03/2019 20:01, Mark Rotteveel wrote: And that is not the point of this thread. I know it is a 16 bit unsigned short inside Firebird, and I know how the value works. My question is about the **encoding** in the data buffer obtained from the native client. I want to avoid incorrectly implementing the support in Jaybird. And FYI, In the wire protocol 16 bit integers are encoded as 32 bit integers, and the length of the buffer from fbclient (12 instead of 10 bytes for TIMESTAMP WITH TIME ZONE) seemed to suggest it is in native as well. However as I said in my reply above, it probably is just padded to a multiple of 4. In any case it's 'yet another' way of addressing the 'timezone' problem, and it's not playing nicely with any of the other equally bad methods adopted by other engines and other interfaces. That it IS using 12 bytes over the wire is perhaps another reason that simply making it natively work with second accuracy rather than minute would have been a step in the right direction? At least that would be more compatible with some of the other approaches and make cross working a little easier. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Timezone id in big endian clients?
On 04/03/2019 17:01, Mark Rotteveel wrote: Interesting bit of information: in the wire protocol, the time zone id is encoded as a signed short, which results in for example Europe/Moscow (id = 65064 or 0xFE28) being encoded as bytes 0xFE28 due to sign-extension, in little-endian native, the buffer has 0x28FE. This suggests that native only uses two bytes plus two padding bytes. It is only 16 bits ... It's restricted to minute accuracy on a fixed offset so is no use for storing mean time offsets which are in seconds, and the TZ identifiers are just an offset from the 1440 minute fixed offset. At some point we will need to be able to handle REAL TZ offsets and pre-1970 data such as the initial switch from local mean time in seconds to the timezone managed time at the location. This current bodge is of little use outside handling current times and even then it fails if the TZ data changes between storing the offset time and reading it. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird 4: Could not find acceptable ICU library
On 04/03/2019 14:08, Adriano dos Santos Fernandes wrote: I suppose that any "real" dependency here is to transform TZ-id to string and vice-versa. As that is present in IUtil, changing that to require the server (or transform IDs to string in data structures and wire protocol) would be a significant architecture change that for me is going to make things worse in the end. Since there is no guarantee that the data being served locally matches the server or other clients then it's just making matters worse anyway. There needs to be a reliable single source that can be referenced and in the absence of anything else this has to be the server. That is until such time as we can rely on the local source being able to reliably identify it's provenance. The VERSION of TZ data being used is an essential element that currently is a complete gamble ... -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6011) Enabling legacy authentication in Windows installer leads to less secur config than possible
On 26/02/2019 16:20, Mark Rotteveel wrote: UserManager = Legacy_UserManager I think had the ", Srp" in the earlier version and it's that which I was adding back first ... leaving it out THEN AuthServer = Legacy_Auth, Srp is not causing a problem! Specifying UserManager = Legacy_UserManager will allow you to manage (create/drop/alter) legacy authentication users only. Using UserManager = Legacy_UserManager, Srp will allow you to manage both, while legacy is the default. It should not affect authentication behavior as far as I'm aware, though. I can now confirm that on 3.0.4 I can add ', Srp' to enable SRP as a second option and things are running fine. On 3.0.2 as shipped with the current stable distribution of SUSE the only way to get legacy to work is to drop Srp from all three settings. I can't see any reason that SUSE could not be rolled forward to 3.0.4 on the stable release? And that is the correct path for this but I'll update my own cribsheet giving access to the 3.0.4 version via the server:database repo on SUSE. ( And I've found the missing video streaming was down to simply needing to reboot the linux based tuner ... it can be a problem at times when one changes something working out just which bit HAS broken ... thg is also up and working again after rolling back to an older build that matches the available hggit ... so now to add some new code! ) -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6011) Enabling legacy authentication in Windows installer leads to less secur config than possible
On 25/02/2019 15:12, Alex Peshkoff via Firebird-devel wrote: page 125 in relnotes 3.0.3 (and I believe not far from it in other versions): Bear in mind in my case I was provided with 3.0.2 via SUSE and even finding the release notes was an exercise. I'm fairly sure that this said specifically DO NOT do something if you are only going to use legacy connections ... yep ... The same red box appears on the 3.0.4 version! 3.0.4 copy I am looking at now only refers to adding the default SYSDBA user and in my case this did exist but the 'Legacy Authentication' section I THINK has a change? UserManager = Legacy_UserManager I think had the ", Srp" in the earlier version and it's that which I was adding back first ... leaving it out THEN AuthServer = Legacy_Auth, Srp is not causing a problem! Now back to trying to find out why streaming video is broken :( -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6011) Enabling legacy authentication in Windows installer leads to less secur config than possible
On 25/02/2019 14:05, Alex Peshkoff via Firebird-devel wrote: Then lets change this question to why the security database in the distribution isn't initialized for SRP (ie having the PLG$SRP table, maybe other things needed). Would it be possible to initialize it as part of the distribution **without** having a user present? That at least would avoid the "Look at the compatibility chapter" error. It will be very useful for a user which started to change configuration file not understanding it to read an instruction instead of continuing in random order. Once again - if one includes SRP in configuration security DB should contain at least one SRP user, if there are no users why include it at all? If I am reading this correctly then I was correct last month when I simply ended up stripping all but the Legacy options from the config file? Rather than following the 'compatibility chapter' which I don't think makes any mention about creating a srp user ... it's about manually creating a legacy SYSDBA/xxx which in the case of the SUSE install already existed. It WAS only when I removed the srp ( and windows ) entries that I could get in. I've a stack of 6 servers out in the workshop each with their own copy of firebird but also using versions on other machines in the stack as backups. The 3050 port is not enabled outside the network and apart from using Flamerobin for manual tweaks, everything else is accessed via PHP, so DO I need to worry about SRP on this setup? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Stable distributions ...
On 25/02/2019 11:14, Mark Rotteveel wrote: You need to explicitly enable hggit to allow TortoiseHg to work with git (and similar for subversion), see https://mcmblog.azurewebsites.net/using-tortoisehg-with-git/ Maybe you need to enable it again? I've been installing hggit for many years ... CURRENTLY the combination of versions of Mercurial, thg and hggit are not matched in Leap15.0 as it's been dropped and I'm wasting time trying to untangle the mess as ALL of my own patches in the website code are managed via that setup :( I may even resort to dropping thg onto a windows machine just to get something working for now but all I've been doing for the last week is trying to get a stable development platform again ... however just had another report of a problem on a client website so need to fix that ... sites that have run for years without any problems. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Stable distributions ...
Currently I have several systems running FB2.5 or earlier along with stable versions of Nginx ( replacing Apache ) and PHP5.x Some are even still running on XP because of hardware that later versions of windows will not support, although it seems the key there is only to run 32bit versions of W10 to gain access to the parallel port. These have been running perfectly stably for years but obviously there are requests from sites to 'upgrade'. Those sites that are now running W10-32bit have now all been configured to reboot over night as a few days of running invariably gets niggles where XP gave in some cases YEARS without a reboot! Some machines have 20 years worth of data back to Interbase days. Currently I've got one of the servers running PHP7.2.5 with FB3.0.4 except it's reporting LI-V6.3.2.32703 Firebird 3.0 in PHP and checking back with yast I'm seeing 3.0.2 for libfbclient2. The problem is I'm getting occasional reports of errors which I HAVE now pined down to the sites running on FB3 while the sites using FB2.5 from another server seem fine. I am presuming that libfbclient2 is accessing both version servers ... but need to work out why the client has not updated. The switch of web stack came about in something of a rush after a disk failure and the server was switched from SUSE13.1 which is no longer accessible repo wise to Leap 15.0 which is the latest SUSE STABLE distribution! So the versions used are as provided and updated by that version. I had the problem initially trying to get access to the FB3.0.2 server, but since everything IS backed up it was simple to just switch to a working FB2.5 server short term ... although that has been some time ago now :( I've been slowly working through getting the development machine upgraded to Leap15.0 which is more problematic than the servers which are running text only and just a web stack. But I now have all four screens working with Gnome (having kicked KDE into the long grass) and on the whole it's now stable - except I can't get access to github via TortoiseHG which has been my stable access route for MANY years and all my local code tracking is on Mercurial in parallel with Git or SVN as required for the third party libraries. I have at least got FlameRobin working despite problems with the problems between versions of graphics libraries and it seems that is part of the problem with TortoiseHG. *SO* I'm a little wary of changing elements which are not directly supported by Leap15.0 such as to FB3.0.4 or later versions of PHP and Nginx along with the development platform based on Eclipse. However the development server IS showing instability and is giving me quite regular crashes due to PHP failing to access the database with a message that Username and password are not set, but a second load works fine! Keeping PHP working with Firebird is a major headache anyway and the interface does need some attention from someone more capable on the C side than me but simply working through the Firebird side what is the SAFE way to update the installation on SUSE Leap 15 and get it all updated and even if I do will it fix the problem. I know I can roll back to FB2.5 and I do now have queries that avoid the new bits in FB3 but with FB4 now appearing there is a further minefield on the horizon with timezone management hand grenades on the side! So just where DO I start trying to get back to a stable platform? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6011) Enabling legacy authentication in Windows installer leads to less secur config than possible
On 23/02/2019 19:21, Paul Reeves wrote: It is linked to the fact that rpm installs don't allow interaction at install time, so the security database is not initialised. IIRC, rpm install 2.5 used to initialize security database using random password for sysdba. Was it changed in 3.0? I only know the suse packages. The security db was left unitialised. The fresh install using the packages currently supported by SUSE Leap 15.0 defaults to 'masterke', and as always the first thing I do is change that via flamerobin. And that has been done in this case. My problem with the 'Compatibility chapter' on the previous install was that I was unable to access the database until I REMOVED Srp from the config file. I have no worries about the legacy system being 'less secure' simply because the only application accessing it is PHP on a local network link and I don't need any more than that so why should I have to do any more than getting a single user working? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6011) Enabling legacy authentication in Windows installer leads to less secur config than possible
On 23/02/2019 11:23, Mark Rotteveel wrote: Yes it is working, even with Firebird 3; except maybe Firebird 3.0.0 and 3.0.1 as I recall there were issues with some of the early versions, but I can't recall if that was pre-release or not. I beg to differ! I've just been working through this in the last few days. Clean machine ... fresh install of SUSE 15.0/Gnome ... all software installed onto new main disk ( data disks separate ). Firebird 3.0.2 along with Flamerobin 0.9.3.1, nginx 1.14.0 and PHP 7.2.5 From previous experience I had stripped the firebird.conf back to AuthServer = Legacy_Auth AuthClient = Legacy_Auth UserManager = Legacy_UserManager WireCrypt = Disabled I can add ', Srp' to UserManager and AuthClient but if I add it to AuthServer then both Flamerobin and PHP fail to connect. FlameRobin gives --- Engine Code: 335544472 Engine Message : Your user name and password are not defined. Ask your database administrator to set up a Firebird login. Install incomplete, please read the Compatibility chapter in the release notes for this version --- So currently I have AuthServer = Legacy_Auth AuthClient = Legacy_Auth, Srp UserManager = Legacy_UserManager, Srp WireCrypt = Disabled And I am connecting and working ... AVOIDING following the Compatibility chapter ... so where am I going wrong? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-6011) Enabling legacy authentication in Windows installer leads to less secur config than possible
On 23/02/2019 08:14, Mark Rotteveel (JIRA) wrote: Personally, I'd also prefer if UserManager order would be set to Srp, Legacy_UserManager, but to support legacy tools that is not really an option. *IS* including the other options in any of the entries tested and working when one is using FB2.x and FB3 systems in parallel? Certainly I've had to strip everything but Legacy_Auth and Legacy_UserManager in order to get my PHP setups to run with FB3! So is FB4 any different? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] What type of UUID we use in Firebird ? i guess Version 1
On 08/02/2019 15:55, Paul Reeves wrote: What type of UUID we use in Firebird ? I guess Version 1 UUIDs in MySQL are really not random https://news.ycombinator.com/item?id=19085189 But are they meant to be random? I thought the only guarantee was that they are unique. I seem to remember a discussion on reordering the elements used to make indexing easier precisely because of the heavy grouping of values in some configurations? I seem to think it was at one of the conferences so must have been over ten years ago ... certainly the only requirement was that they were unique across all machines on the network. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Performance - 2.5 vs 3.0 vs 4.0
On 24/01/2019 09:01, Doychin Bondzhev wrote: Many small installations are on hdd. My first reaction was that HDD is still the more common base for systems, and for large volumes of data I think that will be the case for a while yet? But I've been adding SSD's as the boot disk on my servers for some time now and invariably the LIVE database IS running from that with backup to the larger HDD stores. However I'm fighting a problem this morning with the SSD drive on one of the servers which for various reasons now has btrfs on it because openSUSE think it's the bees knees ... and it's now throwing errors as has happened before on other upgrades. Personally I don't find smearing the OS across all your hard disks particularly safe and I lost a few days work on a previous btrfs crash. I LIKE that I can take an 8Tb disk of data (OK videos) unmount it and remount it on another machine, and the disk with my websites and databases on is similarly transportable if a machine goes down ... and can run from the HDD if needs be. So the key question is not so much the performance of SSD over HDD but rather just what IS the best file system as well? Is it better to keep specialist elements like databases on their own drives? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone identifier to displacement convertion
On 21/01/2019 09:21, Alex Peshkoff via Firebird-devel wrote: That means you have old ICU. Should be something like: SQL> select rdb$time_zone_util.database_version() from rdb$database; DATABASE_VERSION 2012j One would hope that it would return a 2018 version at least 2018i was released on the 31st December, but there is no way of ensuring that the data you are using currently will match that which the OS is working with tomorrow! This method of working with timezones was not thought out at all and is why FB4 timezone handling is badly broken! -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Difference in performance between current_timestamp and localtimestamp
On 29/12/2018 10:10, Adriano dos Santos Fernandes wrote: The cached in request value is now the timestamp with tz, then localtimestamp requires a conversion. That conversion requires to open ICU calendar to get the effective displacement. Please try to alter the session time zone to a displacement (set time zone '-02:00') and see if the performance drop is equivalent. What happens where we are still running the server as ONLY UTC? Which is still the best method of handling any cross timezone data. Can the poor quality tz stuff simply be disabled allowing correct offsets to be used for everything? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Status of time zone support
On 26/10/2018 16:18, Adriano dos Santos Fernandes wrote: On 26/10/2018 12:01, Lester Caine wrote: On 26/10/2018 14:53, Dmitry Yemanov wrote: Yes, it gonna be merged from PR soon. What is the status for time zone support? Is it going to be included in Firebird 4 or not? But the very limited mechanism being included does not support the full range of TZ data as it's limited to minute accuracy. Also it will only support TZ idents. We still have to rely on external tools to provide proper time zone management for historic data ... basically any data pre-1970 requires an alternative to the default TZ based timezone rules, and second accuracy for the base LMT data. AFAIR, you didn't mentioned a single real in-use DBMS doing these things. Is there any point? MOST options for adding timezone to databases are broken in some way, and the SQL standard does not provide a clean method of carrying out the process anyway? The crude way that browsers provide timezone information has always been broken, and provides no information as to the timezone being used by the client, so providing information on events in the past or future has no idea if the client is in a DST zone or not. MOST 'timezone' mechanisms simply follow this process and so historic data is simply broken! Bodging in a another half baked solution in Firebird is a joke! At the very least it should be able to handle data in a clean way, and it's date/time base already provides a cleaner solution than some other engines so why wouldn't any TZ extension? TZ itself is broken which does not help matters, and a clean reliable data source is long overdue. Firebird COULD provide a base for that so that normalized data can be relied on into the future. Using a base that has no record of the version of data used to create it is simply playing roulette with both historic and more critically FUTURE event data! That both DST transitions and even simple offsets are being changed regularly for political reasons needs a mechanism that can flag that normalized data at the very least may need to be reviewed, and having no idea just what timezone data is being used is somewhat pointless? Being able to validate stored offsets against current offsets is an essential part of a reliable TZ management process. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Status of time zone support
On 26/10/2018 14:53, Dmitry Yemanov wrote: Yes, it gonna be merged from PR soon. What is the status for time zone support? Is it going to be included in Firebird 4 or not? But the very limited mechanism being included does not support the full range of TZ data as it's limited to minute accuracy. Also it will only support TZ idents. We still have to rely on external tools to provide proper time zone management for historic data ... basically any data pre-1970 requires an alternative to the default TZ based timezone rules, and second accuracy for the base LMT data. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] LOCALTIMESTAMP name
On 23/10/2018 15:43, Adriano dos Santos Fernandes wrote: AFAIK, but I may be wrong, "TIMESTAMP" is not a correct single word too. There has been a similar debate over TIME ZONE and the RFC for tzdist specifically adopted TIMEZONE to get around the need for an underscore. TIMESTAMP became the standard some time back but there will still be pockets of users who split it ... but certainly LOCAL_TIMESTAMP makes a lot more sense as a result :( -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 06/06/18 15:12, Adriano dos Santos Fernandes wrote: I understand that is a bit weird to have the end data defined to the . seconds, but it's the max. Firebird precision. If precision increases, it would be increased too, of course. That is part of the point. It is only valid within Firebird. Ideally we need 'next' outside of Firebird. There are some places where 'between' works and others where 'gt/lt' IS the correct process in order to remove problems cause by differing precision settings. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 06/06/18 13:12, Mark Rotteveel wrote: BTW: earlier you complained about it being fractional, and now you're complaining about the precision of those fractions not being precise enough? I don't think I understand what you're arguing for and what the problem is, unless you are arguing that the range end should be exclusive, so [start, end), which means that "end" is the same value as "start" of the next one, eg While internally time is limited by the precision of 100 microseconds. externally this is not the case. So the internal 'end timestamp' can't be used externally without some care. But I don't see why 'between' is so important here. In many cases you are ONLY worried about the offset for the timestamp so an internal GT and LT is much more logical ... AND works better when simply using the rules direct ... no need to populate vast tables of transitions ... This is useful where you have more than one set of rules so you can select the right version when processing and upgrade to a new version ... If you update the table of transitions before establishing if any data needs amending you don't know what the original data was ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 06/06/18 09:07, Jiří Činčura wrote: That is still no argument to leave the end of the range out of the provided information. Including it will have little to no cost, and provides full information in one record, which makes it self-contained and easy to understand. Could it be that the data will be stored without "end" column and for convenience a view would be provided? I totally get Lester's point. The end column kind of breaks normalization. There is little reason for creating a database of every transition for every timezone and storing them. One simply processes the rule set and creates what is needed on the fly. And for the majority of non-dst timezones the rule is the transition ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 05/06/18 11:34, Mark Rotteveel wrote: Is there any reason why post-1970 time zones need second resolution for zone offsets? Or is there any other strong argument why second precision is needed? To be honest, I don't see why we should cater to an extremely uncommon minor use-case which will likely be of no interests to the majority of Firebird users, and that will be fraught with so many complications that you'll probably need your own solution anyway if you need full precision offsets pre-1970s. The TZ database does not guarantee correctness nor completeness of its information before 1970, so using second precision there will probably only lead to a false sense of precision. The current argument with the way the TZ database is managed is a separate problem. On one hand, one should perhaps make ANY application give an error when trying to return ANY offsets for dates prior to 1970. There IS no guarantee that the rule set being returned is correct as much because the 'default' set of timezones no longer contain documented transitions for pre-1970 dates if that gets in the way of simplifying the post 1970 data. Yet SOME timezones have accurate transition data back to the 1800's. There is nothing to indicate just which ones are right prior to 1970 and which are truncated. But increasingly the data is being enhanced with new material that has filled in many of the gaps. And for a period in the late 1800's to early 1900's many of the well documented transitions require seconds accuracy. The first problem IS establishing just what view of is being provided by the tools being used, and the fact some Linux distributions don't bother even updating the current transition information does not help at all. But moving forward, the tzdist requires that a rule set includes just what range of dates it is valid from and to. The hole in that is just what does one use prior to that initial date, and it SHOULD be the LMT for the location you are look at. THAT is outside the scope of the TZ database and comes under the banner of the geolocation lookup which would return a valid timezone identifier and a location coordinate that gives the LMT value. YES you can ignore all of that and simply stop the calendar at 1970 ... that would be the reliable way forward ... but with the volume of material being archived even just relating to the 1900's, some means of coping with the documented offsets should be available. This IS a whole database of material in itself as even the timezone identifiers are yet another controversial area, but if the basic elements are correct then Firebird COULD host a more accurate setup than any of the other engines ... and adding the geolocation element even helps get the calendar right. The fact that we use 'day.time' natively already makes Firebird ideal for sorting a lot more than just timezone! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 05/06/18 16:27, Adriano dos Santos Fernandes wrote: Maybe it is just me, but it seems we are now having discussions that should have been had and resolved before implementation. In my whole life, I only see things being done when someone *really does* it. That discussion you were talking, before implementation, if nobody implemented it, it would now be on one of two extremes: silent or that b*t, does not work in another calendar, does not work outside earth, does not work in my application if I do not change it... But really test the implemented version, nobody does... Or simply carry on using UTC timestamps exactly as they currently are and manage timezone display using external tools which DO respect at least some of the edge cases. I do not recall seeing any discussion on the ground rules for handling timezone offsets prior to an implementation being proposed? It could then have been pointed out that the SQL rules simply don't work in a lot of cases. The idea of listing the end time assumes that there will only ever be 4 decimal places of second accuracy, but the involves cropping current time systems. Avoiding the problem makes a lot more sense. Guide lines created in RFC7808 address the majority of the problems created by poorly thought out 'standards' like that included in the SQL book. That RFC provides a clear set of rules to handle the very things that are being reinvented currently ... and any timezone management package should at least be capable of the same level of accuracy. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 05/06/18 09:39, Mark Rotteveel wrote: On 5-6-2018 10:16, Lester Caine wrote: On 05/06/18 08:50, Mark Rotteveel wrote: That naming doesn't make much sense to me, and I actually found the RULE_START and RULE_END naming pretty clear and self-explanatory. Except that it's not the rule itself, but the transitions within the rule ... I'd still like to know why there is a need for the 'end' anyway as the next transition already contains that. Because that is easier when doing queries... select * from ... where current_timestamp between rule_start and rule_end. But you have to use 'current_timestamp' to find the 'rule_start' and 'rule_end' and for many timezones 'rule_end' will simply be the current generic end date returned by the tz database. It requires a little bit more processing than simply pulling out two numbers for a single transition. You normally need the data from the next transition in any case. Your example would be much better as select * from ... where date between tzoffset(tz, date, current_start) and tzoffset(tz, date, next_start) And we can also have tzoffset(tz, date, current) and tzoffset(tz, date, next ) And I've still not had anybody explain why the removal of seconds from the offsets is seen as a good idea? Why is it a bad idea? Because the first transition of every rule set is from LMT to a standard time of some sort. All normalizations to UTC time prior to various times in the last 200 years involve a seconds based correction. SO if one is doing any historic work, one has to ditch many current methods ... because they only work from 1970 ... and do things a different way. At the very least, the documentation has to SAY when the built in procedures can be relied on, and when one has to ditch them. At the same time adding the fact the dates are all Gregorian would be useful. Knowing that historic changes TO Gregorian dates need special treatment would be useful ... I only learnt that Russia was still on the Julian calendar until the early 1900's this week ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 05/06/18 08:50, Mark Rotteveel wrote: That naming doesn't make much sense to me, and I actually found the RULE_START and RULE_END naming pretty clear and self-explanatory. Except that it's not the rule itself, but the transitions within the rule ... I'd still like to know why there is a need for the 'end' anyway as the next transition already contains that. And I've still not had anybody explain why the removal of seconds from the offsets is seen as a good idea? It may interest some people that tzdist-bis list is discussing some of the edge cases on the rules currently while they put TZIF into a formal RFC standard. These standards are not ideal but do provide a common set of rules such as names of fields ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 30/05/18 01:45, Adriano dos Santos Fernandes wrote: RULE_START 2019-10-20 03:00:00. GMT RULE_END 2020-02-16 01:59:59. GMT ZONE_OFFSET -180 DST_OFFSET 60 --- It list all transition rules from the start to the end date, including the pre-start and post-end in the same rule set of start and end respectively. I'm deliberately returning the timestamps in GMT time zone, but they can be easily converted to the wanted one. GMT is now somewhat archaic. The tzdist standard uses Zulu time for all data, so 2008-01-01T00:00:00Z is the standard format. Since you are producing an 'expanded' style format, there is no need to have the RULE_END in every entry ... Just the 'onset' time as defined in tzdist. The fractional times are somewhat strange since the rules are all seconds based. Take care that some DST offsets are -ve but that is because summer time is 'standard' in those locations ... not winter time. And I still find the BODGE of dropping the second data from the offset makes this only useable for current data. The first transition of EVERY timezone has a second accurate variation so one has to revert to an external set of rules for all dates prior to 1970 just to be safe! The use of 'expanded' style only really works for short periods of time anyway. You do not really want to handle the whole of the 20th century showing every transition. The question remains as to just what 'start date' the limited format that is proposed here is designed to be used from. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 15/05/18 10:18, Alex Peshkoff via Firebird-devel wrote: Look how MSK time zone was changed at 2014.10.26 https://www.timeanddate.com/time/zone/russia/moscow I think about something like this: ID Name Valid from Offset ... MSK 2011.03.27 02:00 +4 ... MSK 2014.10.26 02:00 +3 Is it makes sence ? Can it be instead hardcoded in the function(s), describing MSK timezone? I ask cause building a system that describes well former whims of our government appears useless - I believe they can invent something else in the future which will be out of scope of system built to describe previous solutions :-) But if we have no other way to describe MSK timezone (i.e. with random changes of offset at given moments of time) - in that case this will be required. All post 1970 changes to offset's are logged via the TZ database and the Moscow time changes are based on the 'Russia' rule set ( which is used by several timezones! ) up until the 2014 change when Europe/Moscow diverges from the generic rule set. Many time zones use one of a sub set of generic rules where they are associated with different locations. MSK is actually used in several timezone rule sets. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 14/05/18 14:02, Adriano dos Santos Fernandes wrote: So he updates original db and ICU and have what he wants. That only works if TZ is installed using the backzone data, and as you have seen this is not the case. The real world solution for this IS TZDist ... and you would simply patch any locally cached data from that. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 14/05/18 13:55, Vlad Khorsun via Firebird-devel wrote: Can I register a new rule for time zones without updating Firebird and the backup/restore process? If so, is there a DLL syntax for registering a new time zone rule? This is interesting and valid question (as for me). With such possibility Lester could add missed historical rules, i think. One of the items on my back burner is a Firebird powered TZDist service which would provide versioned sets of rules and the check facility for individual timezones. The data would simply be a Firebird database ... The problem as always is lack of time due to trying to play catchup with all the other non-BC changes being made to the whole infrastructure! I'm documenting the notes on this service on the http://timedb.co.uk site which is where it is intended to host it. But local copies of the data would allow off-line use and TSDist would then provide patch updates to that data without needing to retransmit the whole TZ database every time! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 14/05/18 13:22, Adriano dos Santos Fernandes wrote: Can I register a new rule for time zones without updating Firebird and the backup/restore process? If so, is there a DLL syntax for registering a new time zone rule? Serious, you want resgister custom time zones rules? I read that simply as if TZ add new timezones how are the ones hard coded in Firebird updated ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird's compatibility with real world.
I started writing a description of the problems with Firebird's current date and time functions and the extra problems the limited timezone extension introduce, but it was getting too long. The bottom line is that I LIKE the fact that Firebird works with a day element and a time element, but neither are directly suitable for my own historic data, so adding a restricted timezone function is academic anyway. Just what the limitations are need to be documented fully! The one can decide if one can use them at all ... I started http://timedb.co.uk some years ago to start documenting all the problems and originally to provide a place for material that was at that time being sidelined on the TZ database. Paul has now at least backtracked and does allow pre-1970's data to be added even if it's use is a lottery with only primary id's displaying it :( My main problem with all of this in the simple lack of acceptance by many people that the version of the data being used IS important even with current data. How many computers currently are correctly showing the North Korean local time? and many countries continue to debate on whether to continue with DST at all, rule sets change several times a year. http://timedb.co.uk/wiki/view/Firebirds+compatibility+with+the+real+world has grown a bit from it's title and in reality I need to split out the Firebird specific bits from the rant, but hopefully someone will be interested enough to appreciate the details and I welcome any contributions to site! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 12/05/18 15:28, Adriano dos Santos Fernandes wrote: The discussion is going to a place that the next part will be about time zones as we know not works outside of Earth. The current offering is not practical in a number of current real world applications! And the same is true of other databases 'solutions' as well. As it currently stands it's no use to my applications so no reason to even bother testing it ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 12/05/18 07:43, Mark Rotteveel wrote: Like Macau, a lot of historic data has been added and for databases handling historic material it would be nice if there was a way to have matching correct offsets? Or we ignore the builtd in tools and cintinue to rely on external functions ... To be honest, I think the majority of our users will have no need for correct pre-1970 time zone data. I suspect none of the major database provide it either. Lets not get hung up on these esoteric parts of time zones. In part I can accept that, but short cuts need to be documented? It is not common knowledge that TZ data IS questionable prior to 1970, and with the amount of research being done on even just the second world war, knowing that time one is normalizing needs knowledge of the situation at that time. My digging into the German occupation of the Channel Islands threw up the errors there with local time being adrift from the rest of the UK! Dropping seconds accuracy and merging timezone into the same restricted data field is a shortcut that has major limitations. First, prior to standardised time we assume mid-day happened when the sun was directly over head, so the rule for the whole world was LMT. Using Greenwich as a timing point may only date back to 1675 and was only officially adopted in 1880 but it is the current base. America joining in 3 years later and the basis for timezones was established in 1884. So any date prior to 1884 do not have timezones and are identified by their LMT offset. While time prior to the introduction of accurate measuring devices may be somewhat academic and millisecond accuracy totally pointless, that early 19th century time had well documented differences of seconds. So any NEW facility should at least permit those variations to be handled. Or simply say 'the Firebird TIMEZONE extension is only accurate for dates post 1970'. In my book a normalized time consists of FOUR elements. UTC based time, offset, rule set, version. I don't think any of the current 'solutions' offered by any database respects this and so even for CURRENT diary events they have no way of flagging when the calculated offset has been compromised. TZdist was an attempt to plug these holes, but has yet to even start being used despite the fact that it is the perfect solution for time management on 'internet devices'. I have also been advised of a new ietf task group to address the Geolocate Extension and the Time Zone Information Format bu these will be some time reaching an RFC stage :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 12/05/18 00:32, Adriano dos Santos Fernandes wrote: On 11/05/2018 19:48, Lester Caine wrote: There is no information on those pages covering just what way the base TZ data is processed. I think it is probably safe to assume that it is unusable for providing timezone offsets prior to 1970 which is the current target of TZ itself, so it would be nice if there was a suitable warning somewhere? But some way to restore the pre 1970 data would be useful! ICU has the latest database here: http://source.icu-project.org/repos/icu/data/trunk/tzdata/icunew/2018e/ The original one:https://www.iana.org/time-zones (2018e from 2018-05-01). The IANA files mention back zones. If you have more accurate data, I'm sure it would be possible to contribute there. I think I have had confirmation that the stock ICU distribution does not include the backzone data, only the timezone id's that have been stripped in the zone1970.tab list. I have contributed data which you will see in the backzone file and which we know is correct, but instead we get data prior to 1970 for the wrong timezone and no indication that the data is wrong! Paul refuses to include pre-1970 data as it 'may be incorrect', but the result is that one has to assume ALL pre-1970 offsets are incorrect instead, despite people putting a lot of work into researching the missing pre-1970 data ... From TZ file history "A new file 'backzone' contains data which may appeal to connoisseurs of old time stamps, although it is out of scope for the tz database, is often poorly sourced, and contains some data that is known to be incorrect. The new file is not recommended for ordinary use and its entries are not installed by default. (Thanks to Lester Caine for the high-quality Jersey, Guernsey, and Isle of Man entries.)" Like Macau, a lot of historic data has been added and for databases handling historic material it would be nice if there was a way to have matching correct offsets? Or we ignore the builtd in tools and cintinue to rely on external functions ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 11/05/18 23:32, Vlad Khorsun via Firebird-devel wrote: 12.05.2018 1:12, Lester Caine wrote: On 11/05/18 20:37, Adriano dos Santos Fernandes wrote: * PST is Pitcairn or Pacific, with the same offset, they are considered different time zones ICU data is derived from TZ data hosted by IANA (http://site.icu-project.org/tznotice). I really can't answer questions about specific time zones, but really grateful if we can clarify them. The abbreviations are not defined to be unique. Only a subset of them can be used as identifiers, the rest are only 'defined' when associated with a particular rule, so are only suitable for displaying. Personally I need to see the different UK/Ireland timezone idents giving the right offsets prior to 1970 and that depends on how the ICU extract has been processed. It's not a case that 'the ICU data is derived from TZ data', it also depends on how the TZ data is processed by ICU and I'm not seeing ANY information on that. For the Irish area we then get IST standing for Irish Summer Time prior to 1968, and Irish Standard Time after that. Nothing is easy on any of this ... and IST is used in a number of other rules with similar differences in expansion. A quick scan of the ICU files would suggest 'IST' is used by them for India Standard Time but it would be nice if there was an easy way to confirm that? Certainly I'm not seeing data in zoneinfo64.txt for Isle of man, Jersey, Guernsey or Belfast so I suspect they are not valid pre-1970 :( Probably, this answers on (some of) your questions http://userguide.icu-project.org/datetime http://userguide.icu-project.org/datetime/calendar http://userguide.icu-project.org/datetime/timezone There is no information on those pages covering just what way the base TZ data is processed. I think it is probably safe to assume that it is unusable for providing timezone offsets prior to 1970 which is the current target of TZ itself, so it would be nice if there was a suitable warning somewhere? But some way to restore the pre 1970 data would be useful! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 11/05/18 20:37, Adriano dos Santos Fernandes wrote: * PST is Pitcairn or Pacific, with the same offset, they are considered different time zones ICU data is derived from TZ data hosted by IANA (http://site.icu-project.org/tznotice). I really can't answer questions about specific time zones, but really grateful if we can clarify them. The abbreviations are not defined to be unique. Only a subset of them can be used as identifiers, the rest are only 'defined' when associated with a particular rule, so are only suitable for displaying. Personally I need to see the different UK/Ireland timezone idents giving the right offsets prior to 1970 and that depends on how the ICU extract has been processed. It's not a case that 'the ICU data is derived from TZ data', it also depends on how the TZ data is processed by ICU and I'm not seeing ANY information on that. For the Irish area we then get IST standing for Irish Summer Time prior to 1968, and Irish Standard Time after that. Nothing is easy on any of this ... and IST is used in a number of other rules with similar differences in expansion. A quick scan of the ICU files would suggest 'IST' is used by them for India Standard Time but it would be nice if there was an easy way to confirm that? Certainly I'm not seeing data in zoneinfo64.txt for Isle of man, Jersey, Guernsey or Belfast so I suspect they are not valid pre-1970 :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 11/05/18 17:31, Adriano dos Santos Fernandes wrote: Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md So there is no provision to support the initial LMT offsets for those rule sets that provide them? These are second accuracy and for example the new rules for Asia/Macau moved that one from 7:34:20 in 1911 to 7:34:10 in 1904. That is the first time that region started using a centralised time offset rather than Local Mean Time. Most timezones have a second's accurate initial offset, and some have a number of them as the 'novelty' of railway and similar times changed to more centralised and then national times. Reading between the lines, the rule sets used are those provided by ICU rather than the the Oracle approach of providing it's own external files built from TZ distribution versions? Only the full TZ/backzone table with it's own numeric mapping is compiled in the source code? So it is the ICU library that needs hacking to provide earlier views of the rule sets? Has a way of identifying just which rule set is being returned been identified so we can query it and store it with the data? (I'm currently using PHP's library to handle timezone data - and only returning UTC normalized values to the database) Another restriction on timestamps could do with being addressed. Historic genealogical dates can predate January 01 1 so it is quite common to switch to a different date/time library based on unix epoch anyway to make managing the data easier ... this is of cause always a UTC value. It's been so long since I made that switch I'd forgotten that many fields are bigint now rather than timestamp anyway. If time permitted I would build my own TZdist server using Firebird as the database and managing the history of TZ changes in a way that one could identify where normalised data needs refreshing. All the data is readily available and the API for TZdist while cumbersome is elegant when supplying raw offset data to work with. It SHOULD be able to identify when an event's UTC time has changed due to a short notice change of offsets and does not need to be restricted to the limitations of the TZ release process. https://tools.ietf.org/html/rfc7808 if you have not seen it. It provides nice tidy definitions of many of the key terms. It also fails to define just how time zone names are created3 leaving that to the 'publisher'. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Problems with Timezone adjusted data
On 11/05/18 16:07, Adriano dos Santos Fernandes wrote: On 11/05/2018 11:55, Lester Caine wrote: On 11/05/18 15:44, Adriano dos Santos Fernandes wrote: If the machine clock goes back one hour because of a DST transition, the current time overlaps and the same set of numbers are used twice. No. We are talking currently with a normal 64bit TIMESTAMP ... The comparison with Oracle is that they use a simple TIMESTAMP where possible and add the timezone information outside of the stored data. Oracle has TIMESTAMP WITH TIME ZONE. Which as I have said is of little use if you don't know what rule set version was being used when the timestamp was stored! Only storing a clean UTC timestamp will give you an accurate consistent time base. Checking Postgres again, and as I thought, their 'with timezone' is simply a offset stored with the original timestamp. So you have no idea what 'timezone' was used to create it! So should not be called 'with timezone'? Just what is Firebird storing for the timezone element? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Problems with Timezone adjusted data
On 11/05/18 15:44, Adriano dos Santos Fernandes wrote: If the machine clock goes back one hour because of a DST transition, the current time overlaps and the same set of numbers are used twice. No. We are talking currently with a normal 64bit TIMESTAMP ... The comparison with Oracle is that they use a simple TIMESTAMP where possible and add the timezone information outside of the stored data. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Problems with Timezone adjusted data
On 11/05/18 14:27, Adriano dos Santos Fernandes wrote: On 11/05/2018 10:10, Lester Caine wrote: Forgot to hit reply list ... On 11/05/18 12:30, Adriano dos Santos Fernandes wrote: On 11/05/2018 06:54, Lester Caine wrote: I was having a proper look through the Oracle timezone documentation and thinking 'how can you get it so wrong'. Does it REALLY have a ERROR_ON_OVERLAP_TIME flag because it allows the server local clock to be used raw. Actually this is no different to the current situation without managing timezone at all since for those areas of the world that do implement daylight saving there is an ambiguous hour every year and most of the time it is simply ignored? Log files get overlapping data and one lives with it? Maybe you completely misunderstood it? The thing about ambiguous times is only when converting from string to timestamp. Once it's converted, everything works. I think I am reading it correctly. Oracle has a 'with local timezone' to manage using the servers local time No. It stores the time without a time zone, but considers the time zone as being the "database time zone". Database time zone is not server local time zone. In the end, it's better than our TIMESTAMP WITHOUT TIME ZONE, that one is just it, and when reading it it's considered to be in the session time zone. There is a specific note about the 'problem' in the Oracle notes. Lost the link to the page I was reading ;) See my example to Vlad. So a given timestamp + 1 hour may return the same timestamp/tz, but with different displacements. Only if the raw data is not the same number ... Do not understand you. If the machine clock goes back one hour because of a DST transition, the current time overlaps and the same set of numbers are used twice. I suppose we do not want to add to our timestamps the suffixes 'before dst ends' and 'after dst ends'. The whole point of only storing UTC based data is that the raw number stored is always sequential. Currently when the clock rolls back even Firebird overlaps the times, There is no problem with the current server time. It's always mappable to UTC and hence mappable for any time zone. Except for the overlapping hour or other overlapping changes. but maintaining the server as UTC time things are consistent and accurate. This is necessary only when you deal with times and time zones incorrectly. It's just easier to think the earth as a flat time zone. It is much easier where a system has clients world wide the one standardises on a static base, and UTC is the best base for that. Displaying that information in a local timezone is also easier to manage. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Problems with Timezone adjusted data
I was having a proper look through the Oracle timezone documentation and thinking 'how can you get it so wrong'. Does it REALLY have a ERROR_ON_OVERLAP_TIME flag because it allows the server local clock to be used raw. Actually this is no different to the current situation without managing timezone at all since for those areas of the world that do implement daylight saving there is an ambiguous hour every year and most of the time it is simply ignored? Log files get overlapping data and one lives with it? Over 20 years ago I realised that the only way to run ANY system that involves DST was to use UTC only for the server clock. Later adding international material with additional timezones slot nicely into this approach, and ALL you need is the timezone for the client to be able to display material in that data in local time. Or the timezone for the location if the client resides in another timezone. The main problem is that most people only think in 'local time', so when you set a meeting for 9AM just which 9AM is not a factor. Moving that meeting a week is only a problem if there is a DST transition about to happen, and with these not happening at the same time if the meeting is between staff from various timezones the new meeting may be an hour either way depending on what transitions have happened. Even discussions on the TZ list have a camp where it is 'not our problem' what happens in the real world, but when you are creating a timetable of events over a period of time with links to different international locations then a change to one transition - while highly unlikely - should at least flag that there IS a conflict that needs correcting. In order to do that one needs to know that the data used to create the calendar (version 2018e) has now been replaced by something else. In a database it is the historic material that needs to be SAFELY managed. Coming up in the next TZ update will be a change to the Macau rule set that completely rewrites the historic transitions. While it is unlikely this will affect any current data sets world wide, there are many people working through paper archives just like P Chan has done for Macau to correct the historic rule sets so once a system is in place that ALLOWS easy normalization of data it should protect the integrity of that data. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 22:30, Leyne, Sean wrote: No other database engine is maintaining various versions at the same time. Fortunately. I believe that Oracle is. I already put here link describing that when tz db is updated, times may change if used in wrong version. I think I misunderstood which "version" you were referring to. Oracle uses two external files of timezone data. If they use the larger format file then they can't transfer that data to a system using the smaller format file. The smaller file has only a subset of timezone ID's and even the large one is missing valid backzone id's and even some main list entries! The versioning only seems to affect the format of the iles, not the content. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 19:30, Vlad Khorsun via Firebird-devel wrote: The key is that DST doesn't affect given time zone. TZ could be "standard time" based (such as MSK) or "DST based" (such as MSD or IST you speak about) but TZ itself is not changed with DST start\end. What is changed - is what TZ should be used. But we (I) speak not about it. Officially a TZ 'timezone' defines unique set of rules for the OFFSET changes for that 'location' and under the current practice, any timezone that has the same rule set since 1970 has been merged to reduce the number of identifiers used in TZ. Some of those rule sets DO diverge prior to 1970, so unless you have an OS that is using the backzone file the rule set used as a base will be used rather than the actual changes to the offset for the merged rule sets. In many cases timezone is used - incorrectly - to describe the offset and not the set of rules plotting the changes to the offset. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 18:47, Adriano dos Santos Fernandes wrote: 99.9% of our users will use the "official" database. And therefore will have no idea just which "official" database they are using. This is just carrying on the current mess that is inherent when one has no idea just which rules WERE USED to create the data. I became involved with the TZ list because a large block of genealogical data I was using was giving conflicting offsets. Over time the historic rules have been change in much the same way the current rules are change. North Korea has just added a rule change but it has still to filter through all the "official" databases so we are all using the new rule, but one needs to know that the UTC time logged two weeks ago for the meeting next week HAS changed! The VERSION of the rule set being used is just as important as the timezone and in a database with many years of data the rules WILL be changing so one needs to know just WHICH rule was applied when the data was stored against now. The material I was working with was essentially trash as no one had recorded the version of TZ that was being used, and as different computer OS's used different version of TZ the whole thing became a mess. Today the CORRECT identification of a timezone rule set is 'tz ident/version' and recording anything less is just a gamble on what you get. I can give more examples of problems which NOT knowing which rule set was used to create the 'international' timetable when local DST times are changed at short notice ... TZDist is intended to prevent this problem, but since there are no active sources we are still stuck with the miss-match of data between different OS's and system. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 18:47, Vlad Khorsun via Firebird-devel wrote: Regions that use Daylight Saving Time (DST) change the time zone name and time during the DST period. The words “daylight” or “summer” are then usually included in the time zone name. The areas that don't use DST remain on standard time zone all year. This view of things is somewhat out of date. There has been a discussion recently on the TZ list over 'Ireland' which uses 'standard time' in the summer, and changes from that in the winter ... hence a -ve DST offset rather than positive. Also DST in the southern hemisphere is reversed and Australian abbreviations don't follow the same guide. Some tools could not handle the -ve DST so it's implementation has been delayed, but the plan is that the correct data will be provided by TZ at least in future. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 17:24, Adriano dos Santos Fernandes wrote: Would that name be the full name like Europe/Amsterdam, or would it also include the imprecise (duplicates are possible) like CET? Both, each one has a different ID. There are multiple abbreviations that relate to different rules. Only the full name gives the individual rule. This is one of the areas that Paul has been modifying the use of abbreviations where in many cases TZ invented them in the first place. The main problem is however simple things like does your version of ICU include or exclude the 'backzone' file. TZ is only designed to be complete since 1970 so the use of TZ offsets and Daylight Saving information prior to 1970 is something of a lottery! It is another flag that needs to be available so that you know if the TZ rules supplied can be used with genealogical data for instance. 'backzone' contains a number of historic abbreviations some of which were inventions and should be ignored, and others are pre 1970 so not visible in some instances. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 17:12, Vlad Khorsun via Firebird-devel wrote: 10.05.2018 19:02, Adriano dos Santos Fernandes wrote: On 10/05/2018 12:53, Lester Caine wrote: ... And how will the version of TZ data be handled! This is a key element that has screwed normalized data in the past, and some way to manage that normalized data needs to be included in the process. If you mean "normalize data" as the fact that people uses an outdated time zone db, insert data in the database on the outdated time zone, and later update the db, the way to normalize data is up to the user/admin. I guess the question is about the case when users have historical data and need to apply old time zone rule to the old data and new timezone rule to the new data. Is it possible\make sence to add a datetime field with "valid from" mark ? Or something like that, some kind of version mark. This is exactly the problem, and using TZ data direct is the only reliable way to manage the CURRENT TZ rules. So as long as we can switch off ICU data then this may work, otherwise it is just another waste of time! In my case all the TZ material is managed via the PHP timezone classes and that uses the TZ data direct so there needs to be SOME management of just which rules Firebird is using 'automatically'? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 10/05/18 16:27, Leyne, Sean wrote: Q's: How will the data be defined? In code or via some external file? If external, how will changes be applied/recognized to running engine instance? And how will the version of TZ data be handled! This is a key element that has screwed normalized data in the past, and some way to manage that normalized data needs to be included in the process. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zones
On 02/05/18 20:22, Adriano dos Santos Fernandes wrote: On 02/05/2018 13:16, Lester Caine wrote: On 29/04/18 21:47, Adriano dos Santos Fernandes wrote: But standard CURRENT_TIME and CURRENT_TIMESTAMP works different than Firebird, as they returns the "WITH TIME ZONE" types. Tweaking CURRENT_TIME/CURRENT_TIMESTAMP for compatibility is problematic. So how do I maintain a functional system where the server has been configured PROPERLY and is using UTC current time? Timezone is not something that should be rammed down our throats if we are already managing it properly with a clean timestamp and managing the display of that data based on a client login or other local time indication. I don't want CURRENT_TIME to be loaded with a timezone. As said, if you want to update server, I propose backward compatibility via LOCALTIME in old versions. If you want a stable (say, I system that should not update), don't update the server too. I think we should agree that for Firebird a expression CURRENT_TIME_AS_IN_THE_SQL_STANDARD will not be good. :) It's funny as you being one of the most interested in have timezone in Firebird not liking to do any easy work on your systems to adjust. My problem with all of this is that in most cases what is being provided in other databases is NOT timezone, but simply a random offset. The whole problem starts with the browser timezone flag which is anything but! There is no timezone flag provided so no way of knowing just what the time will be in a few months time. I'm perfectly happy with the proper solution to the problem which stores UTC normalised times and a correct timezone flag is a separate field. The last thing any good international system needs is storing everything in server local time. A good example I saw only today is parcel tracking where local times are used to display events ... so in my view a parcel departed some depots before it arrived! I see the current 'solution' as anything but useful in the bigger system of things. As of today even the TZ database can't agree on how to identify timezone rules, and names that were used last year have been changed. These also don't match windows names for the same timezone. Just which set of rules is safe to use going forward? Perhaps when a tzdist service is finally available we might have a chance of making some real progress ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zones
On 29/04/18 21:47, Adriano dos Santos Fernandes wrote: But standard CURRENT_TIME and CURRENT_TIMESTAMP works different than Firebird, as they returns the "WITH TIME ZONE" types. Tweaking CURRENT_TIME/CURRENT_TIMESTAMP for compatibility is problematic. So how do I maintain a functional system where the server has been configured PROPERLY and is using UTC current time? Timezone is not something that should be rammed down our throats if we are already managing it properly with a clean timestamp and managing the display of that data based on a client login or other local time indication. I don't want CURRENT_TIME to be loaded with a timezone. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Valid date or not
On 28/02/18 15:42, Leyne, Sean wrote: 3- I would amend my rules to add explicit support for the -MM-DD (but not -MMM-DD) format for legacy DATE and TIMESTAMP datatype. This has been my standard format since the Y2k problems and flags in the user code to switch D and M values if required then convert TO this format. This SHOULD be the default and anything else converts from it. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Valid date or not
On 28/02/18 10:29, Mark Rotteveel wrote: On 28-2-2018 10:54, Lester Caine wrote: Technically, the SQL Standard knows only one format, and that is (slightly simplified): -MM-DD HH24:MI:SS.FF..+/-TZH:TZHM While this is the 'standard' it has the same fundamental flaw that it's use in other standards has. It has no way of indicating if the OFFSET has a daylight saving element! It has always been wrong to use TZ as the description of the offset ... it IS only an offset. I don't see how knowing about the daylight savings is relevant if you're talking about a point in time. You are getting a date+time and its offset against UTC. That is all you need to know to be able to correctly establish that same instant in any time zone at that date. Knowledge of the actual timezone becomes relevant if you need to do calculations (eg now + 5 months), but then **only** knowing that daylight savings is being applied is not sufficient: you'll need to know the actual zone before you can make meaningful timezone-relative calculations. This is precisely the point here ... I have a meeting 9AM local time today, but it's postponed to next week ... if all I have is the CURRENT time offset then it's anybodies guess what the offset will be next week? It is PURELY a time offset ... it is NOT the timezone and so should not be called that. Any addition of an offset facility to a stored date is just that OFFSET not TIMEZONE ... Conversion of data and normalizing to UTC is relatively easy provided the day and month element is easy to identify, but identifying it's timezone rules is much like deciding if it's M-D-Y or D-M-Y :( The timezone rules only become important when you need to perform timezone-relative calculations. For example, I'm in CET (Europe/Amsterdam) when doing 2018-02-28 11:00+01:00 + 5 months, is the result then 2018-07-28 11:00+01:00 (absolute) or 2018-07-28 11:00+02:00 (timezone-relative)? It depends on my business needs. The 'problem' here is that SQL's focus is on storage (and thus: points in time), not so much on calculations. Use of either absolute or timezone-relative is pretty much a business/functional requirement, which can even vary in a single application depending on the task at hand. I guess the SQL standard didn't want to tackle that complication (because there is no single 'correct' decision here), and choosing offset-based storage makes it simpler, while it is still possible to do both. This just means that timezone-relative calculations are to be handled by applications (or database-specific extensions). ADD ... we need to also know just what set of rules are being used, and this is an area where tzdist has been finalised, but there is no official source of the data. tz ONLY provides a current set of rules valid for dates after 1970, but if we are trying to store historic data normalized to UTC time for easy timeline generation, then we need to know which version of the rules were used for normalising, and if the current rules are different to that. Moving that to a current timeframe, if a meeting is set up with a UTC time for international users and the local DST rules change at short notice ... 'offset-based storage' just makes things worse so why introduce that confusion into the equation at all? Given the number of areas that are actively discussing changing or even dropping 'daylight saving' the problem is enough of a minefield already. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Valid date or not
On 21/02/18 03:02, Adriano dos Santos Fernandes wrote: As part of CORE-5750 problems, I found that Firebird considers '12Mar92' as a valid date (1992-03-12). Should this be considered a bug, i.e., separators should be necessary in this case (12-Mar-92, 12/Mar/92, 12.Mar.92)? I'd consider ANY separator other than a space as an error when using text months. '12Mar92' is at least consistent when spaces and extras are stripped from the data such as '12th March 1992' Timezone text is a considerably more complex problem than month text handling especially since even the TZ database can't agree on just what text to use. The only thing that is consistent is Time OFFSET which uses the '-' sign anyway as a flag. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for timed-zones datatypes
On 15/12/17 15:41, Leyne, Sean wrote: > Lester > >> The problem I had in the past was with meetings being moved over a DST >> boundary. And this was in a SINGLE timezone. The software was normalizing >> everything to UTC time, so that it could be displayed in local time of the >> client, but what was forgotten in the software was which timezone was used >> to store the data. It was JUST stored as an offset, so when the meeting >> moved the change in DST was missed. > > Missed how? > > That seems to be an application implementation issue/bug related to how it > calculated the local time, not an issue of use of UTC offset. If tzdist was actually up and running, when a mobile device re-synced after being offline it would see a change of offset had happened *IF* the software was designed to work with that trigger. THAT part of the system is currently missing, so what you tend to get is that the event is shown an hour out of sync with what is NOW current local time. Participants in another timezone are expecting a live video link at say 11AM, but the local site is either early or late. A major medical conference was messed up a few years back when the Middle East session changed offset due to Ramadan's astronomical observations being at odds with the guess on TZDB. The system needs to be able to cope with ALL reasons for changes to tz offset ... and have some way of flagging it. Not a database problem per-say but the means of converting a timezone time from one location to another needs to be consistent given at times things are very unstable. >> The fact that some timezones have >> DST changes and some do not means that one needs to store the the actual >> rule used and not just an offset. But more than that, one HAS to remember >> the version of the rule being used! > > Let's say we do that. > > Further, let's say that tomorrow the New York state announces that starting > next year there will be no DST. So, the definition of the "America/New York" > time region is affected, which as a timezone, is used by millions of people > in the Eastern US, so all their events would be affected. > > What would you expect a user/developer/the system do to handle this change? > For both pre-change and post-change events? Problem 1 ... A genealogical database has all the times stored UTC normalized. Currently TZDB is not defined as being accurate pre-1970, but a substantial amount of that information is accurate. So we can use it, but as corrections are made, it is important to know just which version of TZDB was used to create the data, and *IF* a change of offsets are triggered by a change in version then either those records are updated and tagged with the new version or they are maintained with the old TZDB version ... Current historic data that has been normalised in the past tends to be suspect because one has no idea just which version of TZDB was used to create it, and Paul has culled a lot of historic data to backzone which some OS's don't even bother to load! Problem 2 ... Some political administration decides to change offset to align with another region, or drop DST as is being discussed in several areas. We have a diary of international events using the old data and JUST like the historic material, we need to be able to detect that the events that were published prior to the change of offset have moved. We may not be able to automatically update the event if it NOW has to happen an hour earlier or later because of other related events such as a satellite link scheduled IN UTC time, but we can at least flag that the event needs to be checked. If a plane has taken off and now lands an hour later because of some local coup one knows what time a meeting was due at but need a new calendar of events to manage the changes. Problem 3 ... You have an event scheduled for 10AM Local summer time but only have the offset from UTC. It's pushed back a week ... into winter time ... but you don't know that this offset involves a DST change so the new time is an hour out! THAT is the one I had on a number of PHP software packages when PHP introduced TZ and ran it from the browser offset! The DATABASE needs to know what the rules are and it is simple to define them as offsets, but personally I don't think that solves anything. It just makes things worse. An offset timestamp is NOT usable timezone data only current offset times. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites,
Re: [Firebird-devel] Support for timed-zones datatypes
On 14/12/17 21:39, Adriano dos Santos Fernandes wrote: >>> This year our politics was speculating to not start DST just days before the >>> initial date. Given the the number of changes to DST and simple offsets is very small, then the occurrence of problems is rare, and on the whole only affects events that do cross multiple timezones, although it has been known for events to have 'moved' time wise while participants have been flying to an event. Since short term changes affect many areas, governments do seem to be getting a little more organised, but there is nothing stopping some new administration changing an offset or DST rule within days of the event. >> Let's discuss that example further. >> >> So, what do you expect the impact of the change to the DST would be for an >> applications. >> >> - You (in Brasília) setup a phone call/meeting, 6 months ago, for today at >> 7am (Dec 12, 2017 == UTC -2) with customers in Mumbai (5:30pm -- UTC +5:30) >> and Adelaide Australia ( 10:30pm -- UTC +10:30) > 7am in UTC -2 => 9am in UTC+0 > 9am in UTC+0 => 2:30pm in UTC+5:30 > 9am in UTC+0 => 7:30pm in UTC+10:30 > Am I making a mistake or you? In my book YES ... because you don't know from that 'data' if any offset changes should the event need to be moved. The problem I had in the past was with meetings being moved over a DST boundary. And this was in a SINGLE timezone. The software was normalizing everything to UTC time, so that it could be displayed in local time of the client, but what was forgotten in the software was which timezone was used to store the data. It was JUST stored as an offset, so when the meeting moved the change in DST was missed. The fact that some timezones have DST changes and some do not means that one needs to store the the actual rule used and not just an offset. But more than that, one HAS to remember the version of the rule being used! So rather than simply adding an offset value to a datetime stamp and calling it 'time-zoned', one needs to properly manage the timezone information and store the identity of the information used to create the offset! Anything else should not be called 'time-zoned' ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for timed-zones datatypes
On 12/12/17 15:20, Alex Peshkoff via Firebird-devel wrote: >> Oracle also mentions inconsistency that may happen when the time zone >> database is updated. If user had previously entered a time in a moment >> that subsequently has updated by the time zone db, it may become >> inconsistent. So it should be updated manually. > > May be also add a new type of trigger that will be invoked under such > circumstances? In some cases such time may be corrected automatically > according to business logic of data, stored in database. The new mechanism for distributing TZ data ... tzdist ... has the potential to trigger events when a particular rule has been changed, but there is a lack of any management of just what set of rules are being used when publishing a calendar. While the current tzdb data does have a version number, the whole database has to be updated when in reality only one or two rules may have changed. The VERSION of TZ data that was used to normalize the UTC timestamps is just as important as the location of the event. The two go hand in hand ... and it's the version data that is missing in the Oracle 'solution' ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for timed-zones datatypes
On 21/11/17 18:31, Mark Rotteveel wrote: > On 2017-11-21 18:31, Lester Caine wrote: >> On 21/11/17 16:42, Mark Rotteveel wrote: >>> >>> If you need that, you also need to store the actual timezone somewhere. >>> PostgreSQL for example also uses an offset for timestamp with timezone >>> (not sure if that is required by SQL standard though). >> >> Firebird currently works nicely simply because you have UTC time and can >> add a second field for location which will provide ACCURATE details for >> displaying local time. Offset only timestamps are simply wrong for many >> reasons! But we need a working tzdist source network before anyone can >> reliably work with timezone data live. The fact that offsets change year >> on year make storing anything with an unqualified offset dangerous and >> the version of TZ data used to create the offset is as important as the >> offset itself. > > Except Firebird doesn't store a timestamp in UTC, Firebird stores a time > without any zone information, so current_timestamp depends on the > current timezone of the server, and values stored depend on the > interpretation (or assigned meaning) from the client application. In > other words, if not carefully controlled, it can become a mess with > varying interpretations without having a ground truth. Which is why the sensible convention is to run the server as UTC if one has to manage data across multiple timezones. Trying to store 'server time' based data falls foul of DST twice a year. Even with a single timezone situation one still needs a coherent clock to manage things like railway timetables, so UTC timetable and Local display ensures there is no problem running services. ( Or the other approach British Rail used to use was never to run trains over night so there were no train movements between 1AM and 3AM ;) ) > A timestamp with timezone gives you more control, because you can still > interpret them in UTC. With the added benefit that a client can - if > necessary - correctly render those timestamps in its own timezone using > standard tools, or insert them with its current timezone offset, and > still have them correctly interpreted by clients using a different > timezone or normalized to UTC, etc. That is actually what PostgreSQL > does: a timestamp with timezone is normalized to UTC and stored in the > same format as a timestamp without timezone, and the offset is a config > or session-specific transformation, while a timestamp (without timezone) > would be not undergo such a transformation. Tikiwiki originally stored data using 'server time' and offset things back to UTC before displaying 'local time' ... except when an event was moved across a DST boundary on one or other clock the result was an hour out. The only way to make it work reliably was to ONLY store UTC based times and DISPLAY times based on the TZ data for the local client. And that location data has to be stored against the clients login data because you can't get it from the browser ... which was the part of the jigsaw that added to the confusion in the first place. Server time is simply local time display for the server location. >> That said, I've not seen what the current SQL standard is proposing, >> just what does NOT work when sourcing data from other databases. > > For the applications I work with, it suffices. I have been caught out a number of times in the past when meeting times were changed over a DST boundary or due to a short notice change of TZ data and NOT storing the information as to WHY a time is not UTC and WHAT was used to generate the stored offset simple creates a mess. Storing a simple static offset is not a solution to the problem of timezone offsets so can we not come up with a solution that has the potential to store real timezone offsets? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for timed-zones datatypes
On 21/11/17 16:42, Mark Rotteveel wrote: > > If you need that, you also need to store the actual timezone somewhere. > PostgreSQL for example also uses an offset for timestamp with timezone > (not sure if that is required by SQL standard though). Firebird currently works nicely simply because you have UTC time and can add a second field for location which will provide ACCURATE details for displaying local time. Offset only timestamps are simply wrong for many reasons! But we need a working tzdist source network before anyone can reliably work with timezone data live. The fact that offsets change year on year make storing anything with an unqualified offset dangerous and the version of TZ data used to create the offset is as important as the offset itself. That said, I've not seen what the current SQL standard is proposing, just what does NOT work when sourcing data from other databases. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for timed-zones datatypes
On 21/11/17 14:55, Adriano dos Santos Fernandes wrote: > Implementing TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE datatypes > as specified in the SQL standard is not a big problem. The only question is just how is the timezone data managed. It is useless simply having an offset value when large areas of the planet still use daylight saving. If I move a meeting from March to April I need to know the real timezone, a simple hour offset is no good at all. It's the same old problem as the offset provided by a browser ... useless for half of the year ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.
On 26/03/17 22:43, Vlad Khorsun wrote: >> That is what I would expect to see ... >> As I stated in SET's reply I would expect to manage the change of a >> default as appropriate, but the fall back should always be the stored >> values? You would only see a 'new' default value if you added it, not if >> you simply changed the metadata? >If i understand you correctly - fb3 works the way you expect. You may look > at my sample at CORE-5507 to see how it works. I'm specifically saying that this bug report is wrong! But perhaps for different reasons? When the F1 field was added, the first record needed an additional field entry adding to comply with the 'F1 NOT NULL' constraint. So *I* would have expected to add an update to provide a value to all of the 'new' 'null' F1 fields on already existing records. That may be 'XYZ' as per the created default when the NOT NULL constraint was added, or something like 'No Default' to retain the pre-default history. I've changed the logic of how this works at this point so both cases now need handling! When ALTER TABLE T ADD F1 VARCHAR(16) DEFAULT 'XYZ' NOT NULL; COMMIT; was actioned, the unpopulated F1 fields have to comply with the new 'NOT NULL' constraint ... although the alternative of leaving out the 'NOT NULL' leaves the option to add the value later and removes the blocking constraint. At this point the table is compliant because F1 exists in all records. Compliance with the constraint is required at this point, so why would you expect when changing the default later that the values of the earlier records then magically change? Without the NOT NULL they would stay as 'null', but with the NOT NULL they already have to have had the conflict resolved ... and have a fixed value of some sort, either the 'XYZ' or an alternate that fits the business logic. So I am quite happy that the 'actual' result from CORE-5507 is correct but I am thinking that at some point we lost the specific requirement to use ALTER TABLE T ADD F1 VARCHAR(16) DEFAULT 'XYZ' NOT NULL; UPDATE TABLE SET F1 = 'No Default' WHERE F1 IS NULL; COMMIT; Ignoring the adding of the new default, the old records have to comply with the new constraint, but it sounds as if there is some 'magic' that is messing up the handling of the stored records to carry out this specific UPDATE? This is perhaps the mention of 'format', where as far as I am concerned there is only one format, the current record layout. I am more than happy that the SQL standard is written on the basis that when a change is made to a record it is physically implemented so that the 'F1' field will be created for every old record and would default to a new default ... but there is probably some discussion as to if that needs the explicate UPDATE or is magically implied by the use of the COMMIT without any other action. If you have some reason to distinguish between the older records and the 'XYZ' default records, then this should be managed specifically, and leaving these as 'null' values without the 'NOT NULL' constraint would perhaps be appropriate for this business logic? Rather than some hidden mechanism that distinguishes different versions of the same record? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.
On 24/03/17 21:42, Vlad Khorsun wrote: > 24.03.2017 21:36, Lester Caine wrote: >> On 24/03/17 17:57, Vlad Khorsun wrote: >>>> What must be done is specified exactly. And we should suppose that in >>>> all other aspects data stored in database should not be changed - >>>What data is *stored* ? >> For any field, the data that is loaded initially or later modified. And >> I REPEAT ... if the field was originally NULL it should remain NULL >> until a specific record update modifies it. It should not be returning >> some DEFAULT value that was added later UNLESS the constraints are >> changed to require the NULL value to be populated, but that should not >> be populated 'magically' and the SQL spec does not override that basic >> function of a value in an existing record? The DEFAULT value should only >> be used to replace a NULL value when a record is added ... full stop. >Engine substitute default value if and only if NOT NULL field was added > and DEFAULT value was specified. Engine not allow to add NOT NULL field > without DEFAULT. If NULLable field was added engine will return NULL's > for that field at existing old records despite of precence of DEFAULT value. > >Is it OK ? That is what I would expect to see ... As I stated in SET's reply I would expect to manage the change of a default as appropriate, but the fall back should always be the stored values? You would only see a 'new' default value if you added it, not if you simply changed the metadata? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.
On 24/03/17 17:57, Vlad Khorsun wrote: >> What must be done is specified exactly. And we should suppose that in >> all other aspects data stored in database should not be changed - >What data is *stored* ? For any field, the data that is loaded initially or later modified. And I REPEAT ... if the field was originally NULL it should remain NULL until a specific record update modifies it. It should not be returning some DEFAULT value that was added later UNLESS the constraints are changed to require the NULL value to be populated, but that should not be populated 'magically' and the SQL spec does not override that basic function of a value in an existing record? The DEFAULT value should only be used to replace a NULL value when a record is added ... full stop. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.
On 24/03/17 15:12, Adriano dos Santos Fernandes wrote: >> Current implementation >> changed well known old behaviour not claiming it as a bug. I'd say it looks >> like >> a bug itself. >> > Changed a bugged design/implementation since 2009. Looks a bit late to > say this. I'm still running FB1.5 on some legacy sites, so keeping up with all this new stuff is a problem. My FB3 installs simply did not work and I don't have time to find out why, but the ONE thing I expect to be consistent is that the data returned in a record is actually what is stored IN that record. The IDEA that a field that can store NULL but not actually return that in the result set is most definitely a bug. If you want to ensure a real value is returned, then the field should be NOT NULL and then the value stored is a real value that is only changed by a real UPDATE of some type. Certainly if a default value is a time stamp then it is no different to a fixed default value once a new record is added. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5507) Wrong value of the new field at the old records, created before that new field was added.
On 24/03/17 08:50, Svein Erling Tysvær wrote: > I would expect changing defaults for fields that didn't have a default > when records were inserted and that haven't explicitly received any > value since, to be something that isn't all too common. Normally, I > would expect running an "update T set F1 = 'XYZ' where F1 is null" > shortly before or after setting such a default (assuming this would set > the value even if issued after setting the default), but this may be > just me and/or Fb 2.5. Note that I never work against huge databases. I'm in the same camp as SET, especially if one is now adding a 'NOT NULL' constraint to a field that contains NULL values. If one is not adding the 'NOT NULL' constraint, then *I* would expect those fields to still return NULL even after adding a DEFAULT 'NEW' because that is value to REPLACE the NULL when ADDING a new record. If this is not the case now, then my understanding is wrong, but in my case adding the the NOT NULL would go hand in hand and the old records HAVE to be populated manually prior to adding it? If later you need change a default then it is up to the business logic to decide if old records change to the new default, and at this point it becomes even more important that the 'unset' fields can be identified from the 'old default' ones? If your logic is now replacing the returned data with a different value to that actually stored, then I would say THAT is a bug. If the stored field is NULL then either the query should return that or contain the logic to display it as something else, it should not be 'magic' depending on some other settings? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Git
On 20/03/16 02:34, James Starkey wrote: > An experienced developer might have 10 or 20 topics on the go - each in > their own separate branches. That way work doesn't cross-contaminate - > you don't want to accidentally upload a half-baked development because > you had to upload an unrelated emergency bug-fix ... :-) > > A developer who does that, experienced or novice, is going to lose > his or her mind. Been there, done that, went nuts. One can only juggle > so much complexity. I'll second that ... especially when one is sharing work with other developers that has not yet even been flagged with a push to the main repo. In my case several third party components each with their own github master. To retain sanity here I keep the repository views of the code ring fenced in their own clean folders and then use 'BeyondCompare' to cherry pick any new work that potentially conflicts with my own developments. The automatic merges done by git may hide things you want to pick up on? This is where 'submodule' access to a third party library can conflict with the version that you are currently working with. Working on a single target program *IS* a lot easier than some of the projects that I'm working with, but having a clean base to build on is essential? And as I understand it, having one 'release manager' controlling the 'master' code branch is sensible? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785231=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: File names with non-ASCII non-ANSI letters
On 16/03/16 11:39, James Starkey wrote: > Or simply restrict database file names to ASCII. It's not like users > have to deal with them, just like they don't have to deal with > identifiers in SQL, or C or Java. As someone linguistically challenged, I have no problem with my own code, but English is not the most used language on the planet, and for many it creates another complication to programming in their own language, so I can understand that 'restrict database file names to ASCII' is as irritating these days as some of the other 'politically correct' things we have to put up with with. That SQL and other programming languages are essentially 'english' is not real case for only supporting 'english' in the 21st century? But many programming languages and os's still can't cope with this problem anyway so perhaps we have to live with that? :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785231=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: File names with non-ASCII non-ANSI letters
On 16/03/16 08:18, Александр Пешков wrote: > Could somebody explain why Firebird is in the filename translation > business at all? > > Imagine a system, containing linux server, using UTF-8 encoding, some > linux clients (also UTF-8) and some windows clients (cp1251, I use > russian sample) and using russian name for database file. W/o > translation filenames, received from windows client, will binary differ > from one, used by linux clients and server, which will cause file open > failure. Given that it's not just Firebird that has a problem with the crap M$ still peddle, more generic solutions exist already. Having used alias.conf for many years and kept the alias to 7bit ASCII characters has been one approach, so perhaps this could be expanded to include several aliases all mapping to the one safely wrapped internal name and path? I do find it strange that people still use OS specific settings on networked systems which could well have problems with that. None of my users even know that it's Firebird providing the grunt, as the hack attempts attest, so shouldn't the inter OS conversion problems be handled in a different way? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785231=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GitHub
On 15/03/16 02:24, Jim Starkey wrote: > The combination of cmake and git submodules would let the Firebird > project to be decomposed in clean, separate, architecturally independent > projects, a win-win all around. The key statement there is the 'combination of'. Although the facilities provides by the git version of submodules have improved, it is reliant on a combination of tools to make things work that we used to have in a single package. http://blogs.atlassian.com/2011/12/git-submodules/ check out the 'gotcha' although I find the problem is more to do with the selected submodules being a particular snapshot, rather than an active element you can update. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785231=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: File names with non-ASCII non-ANSI letters
On 14/03/16 17:57, Adriano dos Santos Fernandes wrote: > On 14/03/2016 14:52, Dimitry Sibiryakov wrote: >> > 14.03.2016 18:32, Adriano dos Santos Fernandes wrote: >>> >> Proofs, please. Not guesses. >> > http://blogs.msdn.com/b/michkap/archive/2005/01/16/353873.aspx >> > >> > > A site that I need to authenticate, fill forms, agree with whatever and > in the end says the page does not exist. Perfect. This is Microsoft you are dealing with ... what do you expect? https://blogs.msdn.microsoft.com/oldnewthing/20050617-10/?p=35293/ refers to the same link and does not require you to log in. Seems any work by michkap has now been wiped? As a result one gets redirected to the login screen. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785231=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GitHub
On 13/03/16 22:03, Jim Starkey wrote: > Splitting the code base would strengthen the underlaying architecture, > spotlight unnecessary dependencies, and promote cleaner and better > inter-component interfaces. And, in theory at least, various components > could be released independently. The fact that git does not handle modular projects at all well was my main objection to being forced to use it. CVS had it's faults, but also it's good points, and being able to create a release made from cherry picked parts of the code tree was one which was blown apart by the git requirement that every part has it's own repository. It's only recently that the idea that 'sub-modules' are perhaps useful to some projects that is concept has started to be developed, but essentially making each element of the code base a separate repo also requires that each is synced and track individually. Anybody remember the good old days when one could hit 'sync' and see which blocks of code had been updated ... one could then simply merge a block or individually scan the files of a block you were also working on. Not something that is easy to do with the current work flows forced on us by git? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785231=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: File names with non-ASCII non-ANSI letters
On 11/03/16 16:53, Leyne, Sean wrote: > The user would be able to name the database anything they want, they would > simply need to have the same name. The problem with that statement is something that caught me out a while back hence asking about the current state of Windows. When moving from windows powered servers to linux ones, the backups were mirrored between the two OS's ... except at that time windows was 'tidying' things by upper casing the first character of the file name. It was a couple of weeks before I realised that file I was restoring from was NOT the current data. There was an alternative with an upper case first letter ... in the same directory. At that point I switched off the windows side and committed to the linux machines, but I never worked out just why the essentially wrong file name was created. On the windows end, the file names were all lower case, but it was a windows application that transferred the files and I've seen similar problems several times on cross OS sites! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://pubads.g.doubleclick.net/gampad/clk?id=278785111=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Positioned attributes in CREATE/ALTER sequence statement
On 08/03/16 17:00, Dmitry Yemanov wrote: > 08.03.2016 19:06, Dimitry Sibiryakov wrote: > >> 05.03.2016 22:28, Lester Caine wrote: >>> It would be nice if we did not have re-order scripts from other >>> databases so >>>NOT NULL DEFAULT '30' >>> has to be >>>DEFAULT '30' NOT NULL >>> in Firebird. >> >> If DY agree, I can commit this change. > > I don't think we need to tweak our parser to please non-standard > database implementations. I think something like this does not cause any problems, but can be flagged as our flexibility when working with material from other sources. If M$ start pushing MSSQL on Linux then making the alternate switch to Firebird as easy as possible makes sense to me. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Transform Data into Opportunity. Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library. Click to learn more. http://makebettercode.com/inteldaal-eval Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Positioned attributes in CREATE/ALTER sequence statement
On 06/03/16 08:51, Mark Rotteveel wrote: >> I don't think that the SQL standard ever imposed an order on these? But >> > both MySQL and Postgresql seem to standardise on the DEFAULT last :( > The SQL standard specifies that the default clause follows the data type > definition. And this is what Firebird does. The syntax in the SQL > Standard is defined as: Ta ... I knew there was a reason ... but I did my talks on inter database operability at the conferences 10 years ago :) But I doubt that MySQL and Postgresql will fix that now :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Positioned attributes in CREATE/ALTER sequence statement
On 05/03/16 13:32, Dimitry Sibiryakov wrote: >Currently parser enforce attribute clauses to have a definite positions in > the > statement. SQL standard doesn't require that. >Do you agree that position-insensitive clauses would be more convenient? It would be nice if we did not have re-order scripts from other databases so NOT NULL DEFAULT '30' has to be DEFAULT '30' NOT NULL in Firebird. I don't think that the SQL standard ever imposed an order on these? But both MySQL and Postgresql seem to standardise on the DEFAULT last :( It's a little less of a problem these days where an abstraction of some sort is used. ADOdb we can ensure the 'datadict' or 'XML' scripts produce the right order, but where initialise scripts use raw SQL it's a difficult one to modify on the fly. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: Tablespaces
On 03/03/16 20:13, Jim Starkey wrote: >> Where i can see a useful segregation is archival data which will never >> be modified. Would be very usefull if all that could be backed up on a >> Slow cycle, and only the real dynamic data kept in the primary table >> space? But one can do most of that by having multiple databases anyway? > > You know, that's a really good idea. Disk is almost free, too cheap to > meter (so to speak). Store something once and just don't worry if it > goes out of style... I spent last night down at the local A to get a problem sorted. I was reading these emails while waiting and was re-thinking the problem of medical data given the size of the piles of paper records passing by ... Where the bulk of that material is old paper and images, digitizing them for easy access anywhere is a 'cost' problem. but replacing with electronic copies creates the sort of 'blob' data that I was talking about originally. It's the sort of static data that perhaps needs multiple storage sites, but does not need to be continually backed up in any database system ... just the index of available material. Content extracted from that data for easy access such as say 'blood pressure and temperature', while part of the dynamic element of the records is essentially a static set of records to which material is added, and it's this element that could perhaps benefit from a different process? 'Non-deletable' data so the pages containing it are essentially static, but with an active final page? I think a large section of data would be better handled like this such as bank transactions or telephone calls. Delete in these cases would be date related, but 'older' pages could simply be left on the system so one can just scroll back through if necessary? What gets my goat currently is the way my bank 'archives' the on-line data to bank statements so one can't easily scroll back, but each of those 'blob's of data could be made accessable with a different approach to the storage? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=272487151=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GitHub
On 02/03/16 11:18, Dmitry Yemanov wrote: > Firebird Project will be moving its repositories to GitHub soon. As someone who has had to put up with the less than useful manor that 'github' handles code on-line can I pass on a useful approach that I've found makes it a lot less painful ;) Mercurial (hg) has had a much better support cross OS since day one, and can handle git repositories transparently via hggit. http://tortoisehg.bitbucket.org/ provides a clean way to view the history of any file, and manage merging with local copies easily on both windows and any linux system. While most installs include hggit ( which may need simply to be enabled ) http://hg-git.github.io/ has the current version, but like everything these days, keeping everything in sync when someone breaks one of the python libraries can be a problem. Is there such a thing as a stable development platform? Just which compiler, package handler and build process should one be using :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=272487151=/4140 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
