Re: [Firebird-devel] Valid date or not
> Exactly what do you mean with this? -MM-DD is already supported. I wasn't sure it was -- it is not a string format that I/we use. Sean -- 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 2018-02-28 16: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. Exactly what do you mean with this? -MM-DD is already supported. Mark -- 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 2018-02-28 19:26, Adriano dos Santos Fernandes wrote: This thread become full of offtopic discussion... Discussion here is about date/time separators, for consistency and correct handling of time zone offsets. As Firebird accepts everything (spaces, commas, minus, etc) as separators, that is very problematic to separate the time and timezone offset portions, as they should be separated by a space followed or not by a minus or plus sign (the sign is optional) and a number. If you follow the format in the SQL standard, then sign is not optional, and there should be no whitespace between the time and the offset. Why not the following solution: offset in a literal or cast from string is only supported with the SQL standard format, all other formats result in an error. Casting to char/varchar of a time with time zone or timestamp with time zone will always result in the SQL standard format. If we want to support alternative formats, we should implement the format-clause of cast. For time without time zone or timestamp without time zone, we should consider deprecating the old formats with the intent of phasing it out, and maybe extending the format-clause with options not supported in the SQL standard to cover the existing formats supported. Mark -- 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
This thread become full of offtopic discussion... Discussion here is about date/time separators, for consistency and correct handling of time zone offsets. As Firebird accepts everything (spaces, commas, minus, etc) as separators, that is very problematic to separate the time and timezone offset portions, as they should be separated by a space followed or not by a minus or plus sign (the sign is optional) and a number. Only that. Timezone is another discussion, break in all ways the current date/time format is also another discussion. Adriano Em 28 de fev de 2018 13:42, "Leyne, Sean" escreveu: > > > > 28.02.2018 16:42, Leyne, Sean wrote: > > > Based on this, and considering legacy FB applications I propose the > > following: > > > > > > 1- The only acceptable string format for the new Date/Time with > > > Timezone datatypes should be the ISO/SQL standard > > > > > > 2- Only legacy DATE and TIMESTAMP datatype would maintain support for > > the legacy date formats. > > > >Does Firebird parser have a problem with rule ' := > > > timestamp>'? > >I can't remember example of timezone that would make parsing conflict > > between time part of timestamp and time zone string. > > First, I was referring to input formats which would/should not require a > CAST, for legacy support purposes. > > Second, I don't really care if the parser could support it for the new > types. > > IMO, FB should not need to support date/timestamp legacy formats for new > data types, expect via explicit CAST to . > > > Sean > > > > -- > 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 > -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdotFirebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Valid date or not
> 28.02.2018 16:42, Leyne, Sean wrote: > > Based on this, and considering legacy FB applications I propose the > following: > > > > 1- The only acceptable string format for the new Date/Time with > > Timezone datatypes should be the ISO/SQL standard > > > > 2- Only legacy DATE and TIMESTAMP datatype would maintain support for > the legacy date formats. > >Does Firebird parser have a problem with rule ' := > timestamp>'? >I can't remember example of timezone that would make parsing conflict > between time part of timestamp and time zone string. First, I was referring to input formats which would/should not require a CAST, for legacy support purposes. Second, I don't really care if the parser could support it for the new types. IMO, FB should not need to support date/timestamp legacy formats for new data types, expect via explicit CAST to . Sean -- 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
28.02.2018 16:42, Leyne, Sean wrote: Based on this, and considering legacy FB applications I propose the following: 1- The only acceptable string format for the new Date/Time with Timezone datatypes should be the ISO/SQL standard 2- Only legacy DATE and TIMESTAMP datatype would maintain support for the legacy date formats. Does Firebird parser have a problem with rule ' := timestamp>'? I can't remember example of timezone that would make parsing conflict between time part of timestamp and time zone string. -- WBR, SD. -- 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
> Should this be considered a bug, i.e., separators should be necessary in this
> case (12-Mar-92, 12/Mar/92, 12.Mar.92)?
My initial reaction was yes, but when I started thinking about/listing my
"formatting rules" and came to realize that "no separator" was a
reasonable/logical extension.
My formatting rules are:
1- A consistent non a-Z separator should be required. The exact separator
should not matter (but have no problem if a restricted set of characters is
defined)
2- Year part is always the last "position"
3- Any (a-Z) characters are considered part of the month abbreviation, also
allowing day/month and month/day. The abbreviation must be 3 characters. And
conform to English abbreviation.
4- in the absence of (a-Z) characters/month abbreviation, the order to the date
"parts" is day, month and year.
Using the above rules the following formats would be supported:
DD/MM/YY, DD/MM/, DDMMYY
DD MMM YY, DD MMM , DDMMMYY, DDMMM
MMM-DD-YY, MMM-DD-, MMMDDYY, MMMDD
Then I did some reading on the ISO 8601 date/time format (which aligns with SQL
standard), and realized that we had a problem! that format is it quite
specific, only allowing -MM-DD. {ignoring time component for the moment).
Based on this, and considering legacy FB applications I propose the following:
1- The only acceptable string format for the new Date/Time with Timezone
datatypes should be the ISO/SQL standard
2- Only legacy DATE and TIMESTAMP datatype would maintain support for the
legacy date formats.
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.
Sean
--
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 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. 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). Mark -- Mark Rotteveel -- 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 09:29, Mark Rotteveel wrote: 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' Common practice disagrees (although it may vary by geographic region!), dates like 12-MAR-92 are relatively common and are a compromise between human readability and machine parsability. There are a vast range of 'common practice', most of which make it difficult to be 100% certain of a date without reference to other information ... replace MAR with 03 and the date becomes a little ambiguous ... 12th March or 3rd December? 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. 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 :( 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. A '+' or '-', not just a '-'. My point was that '-' can be used as a separator and a flag ... in the same standard date. -- 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-2-2018 10:00, Lester Caine wrote: 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' Common practice disagrees (although it may vary by geographic region!), dates like 12-MAR-92 are relatively common and are a compromise between human readability and machine parsability. Technically, the SQL Standard knows only one format, and that is (slightly simplified): -MM-DD HH24:MI:SS.FF..+/-TZH:TZHM or: ::= ::= ::= ::= ::= ::= ::= ::= [ ] ::= By specification, SQL doesn't know other formats, except through a formatted cast. However, even with a formatted cast, the SQL standard requires a separator which is: ::= | | | | | | | 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. A '+' or '-', not just a '-'. Mark -- Mark Rotteveel -- 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] Valid date or not
Em 22/02/2018 12:39, Dmitry Yemanov escreveu: > 22.02.2018 16:41, Alex Peshkoff 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)? >> >> Let's better treat it as standard extension. >> Line is human-readable and as far as I can see does not provide an >> ability to treat it as date in illegal way. > > From another side, I really doubt anyone is intentionally using this > format. So the main question is whether it prevents Adriano from doing > his improvements. > It's not impossible, but totally very annoying case, as there is different but similar ways: Mar12 92 Mar12-92 Then we should invent rules, as the current fix I did makes necessary that the first separator is equal to the second, to not accept 11.01-92. But if empty becomes a valid separator and space is not, both cases above will be invalid. So my preference is to invalidate 12Mar92 as well. Adriano -- 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
22.02.2018 16:41, Alex Peshkoff 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)? Let's better treat it as standard extension. Line is human-readable and as far as I can see does not provide an ability to treat it as date in illegal way. From another side, I really doubt anyone is intentionally using this format. So the main question is whether it prevents Adriano from doing his improvements. Dmitry -- 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 22-2-2018 14:33, 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 argue that we should - by default - only support the formats defined in the SQL standard -MM-dd for date, HH:mm:ss[.[f...]] for time and -MM-dd HH:mm:ss[.[f...]] for timestamps (ignoring options needed for timezone offsets), but doing that would seriously break things. Maybe we should implement the FORMAT-clause of the CAST specification, users can then have explicit control if they want or need a different format. Mark -- Mark Rotteveel -- 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 02/22/18 16:33, Adriano dos Santos Fernandes wrote: Hi! 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)? Let's better treat it as standard extension. Line is human-readable and as far as I can see does not provide an ability to treat it as date in illegal way. -- 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
