Thread
-
ISO dates with European Format
Theo Kramer <theo@flame.co.za> — 1999-09-14T11:22:28Z
I have the requirement for ISO dates with European format and would like to change backend/utils/adt/dt.c:EncodeDateTime() and EncodeDateOnly() to effect this if this is a general requirement. Please advise. -- -------- Regards Theo
-
Re: [HACKERS] ISO dates with European Format
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-09-14T13:40:12Z
> I have the requirement for ISO dates with European format and would > like to change backend/utils/adt/dt.c:EncodeDateTime() and EncodeDateOnly() > to effect this if this is a general requirement. What is "ISO dates with European format"? Is it a combination of ISO date output with European-style input (which I think can be done already), or something else? afaik ISO-8601 is specific about suggested formats, and makes no distinction between European and other conventions. Can you give examples? TIA - Thomas -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California -
Re: [HACKERS] ISO dates with European Format
Theo Kramer <theo@flame.co.za> — 1999-09-14T14:03:17Z
Thomas Lockhart wrote: > > > I have the requirement for ISO dates with European format and would > > like to change backend/utils/adt/dt.c:EncodeDateTime() and EncodeDateOnly() > > to effect this if this is a general requirement. > > What is "ISO dates with European format"? Is it a combination of ISO > date output with European-style input (which I think can be done > already), or something else? afaik ISO-8601 is specific about > suggested formats, and makes no distinction between European and other > conventions. Can you give examples? TIA Sure - coza=> set datestyle to 'SQL,European'; SET VARIABLE coza=> select registrationdate from accounts where domain = 'flame.co.za'; registrationdate --------------------------- 02/06/1997 00:00:00.00 SAST (1 row) The above result is correct for dd/mm/yyyy styles coza=> set datestyle to 'ISO,European'; SET VARIABLE coza=> select registrationdate from accounts where domain = 'flame.co.za'; registrationdate ---------------------- 1997-06-02 00:00:00+02 (1 row) Instead of 02-06-1997 00:00:00+02 If ISO is specific regarding formatting of days, month and year then I feel that the "set datestyle to 'ISO,European'" should give an error. However, I would personally prefer it to format the result as "dd-mm-yyyy". -------- Regards Theo
-
Re: [HACKERS] ISO dates with European Format
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-09-14T14:38:09Z
> > What is "ISO dates with European format"? Is it a combination of ISO > > date output with European-style input (which I think can be done > > already), or something else? afaik ISO-8601 is specific about > > suggested formats, and makes no distinction between European and other > > conventions. Can you give examples? TIA > coza=> set datestyle to 'SQL,European'; > ... > 02/06/1997 00:00:00.00 SAST > The above result is correct for dd/mm/yyyy styles > coza=> set datestyle to 'ISO,European'; > ... > 1997-06-02 00:00:00+02 > Instead of 02-06-1997 00:00:00+02 > If ISO is specific regarding formatting of days, month and year then I feel that > the "set datestyle to 'ISO,European'" should give an error. However, I would > personally prefer it to format the result as "dd-mm-yyyy". Ah! The yyyy-mm-dd order is specified by ISO-8601. wrt Postgres, you are actually wanting European format with "-" as a date delimiter, rather than the "/". As an aside, "ISO,European" does actually have meaning, since setting the DateStyle to ISO only fully constrains the output format, but "European" helps the date parser resolve free-form date input ambiguities by assuming European, rather than US, conventions for ordering of input fields. But back to the delimiter... Date conventions between and among countries vary. The formats we currently have each meet the conventions of multiple countries (not certain about "German", since apparently other Germanic countries do not all share the same convention). There are (at least) two things we could do: 1) Parameterize the delimiter field using a #define constant you can redefine in Makefile.global, Makefile.custom, or configure. Apparently South Africa uses the "-" convention for date delimiters? Or is this a more local or project-specific preference?? 2) Parameterize the delimiter as a global character variable, which can be manipulated by something like "set DateDelimiter = '-'". This is a little nervous-making for me, since you (and every database user) would have the ability to modify the date format to something that Postgres can not read. So we would have to modify the input routines to accept an arbitrary delimiter, as well as the conventional delimiters (both "-" and "/") already recognized. I suppose we could put constraints on the "set DateDelimiter" values to help protect from this... You could also consider massaging the date format as it is displayed by your app, since that would give you full control over the appearance. - Thomas -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California -
Re: [HACKERS] ISO dates with European Format
Theo Kramer <theo@flame.co.za> — 1999-09-14T15:28:21Z
Thomas Lockhart wrote: > Ah! The yyyy-mm-dd order is specified by ISO-8601. wrt Postgres, you > are actually wanting European format with "-" as a date delimiter, > rather than the "/". Yuck on ISO and yup to the rest :-). > As an aside, "ISO,European" does actually have meaning, since setting > the DateStyle to ISO only fully constrains the output format, but > "European" helps the date parser resolve free-form date input > ambiguities by assuming European, rather than US, conventions for > ordering of input fields. > > But back to the delimiter... > > Date conventions between and among countries vary. The formats we > currently have each meet the conventions of multiple countries (not > certain about "German", since apparently other Germanic countries do > not all share the same convention). There are (at least) two things we > could do: > > 1) Parameterize the delimiter field using a #define constant you can > redefine in Makefile.global, Makefile.custom, or configure. Apparently > South Africa uses the "-" convention for date delimiters? Or is this a > more local or project-specific preference?? Pretty much project related regarding the delimiter. We tend to use dd/mm/yyyy locally. I would prefer to not create a specific postgres. > 2) Parameterize the delimiter as a global character variable, which > can be manipulated by something like "set DateDelimiter = '-'". This > is a little nervous-making for me, since you (and every database user) > would have the ability to modify the date format to something that > Postgres can not read. So we would have to modify the input routines > to accept an arbitrary delimiter, as well as the conventional > delimiters (both "-" and "/") already recognized. I suppose we could > put constraints on the "set DateDelimiter" values to help protect from > this... Hmmm, a product I helped develop uses two mechanisms for specifying date style. First the format and second the picture. The format allows swapping of sub fields within a date and a picture to specify the output. Eg. dd/mm/yyyy as a format with a picture of 99/99/9999 or mm/dd/yyyy and 99/99/9999 or dd-mmm-yyyy and 99-xxx-9999. This format allows total control over dates (at least in Western countries) ... I am happy to donate the code... Windows (int the regional settings) follows a similar approach. > You could also consider massaging the date format as it is displayed > by your app, since that would give you full control over the > appearance. True :-). Thanks for the responses. -------- Regards Theo
-
Re: [HACKERS] ISO dates with European Format
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-09-14T15:44:25Z
> Hmmm, a product I helped develop uses two mechanisms for specifying > date style. First the format and second the picture. The format > allows swapping of sub fields within a date and a picture to specify > the output. Eg. dd/mm/yyyy as a format with a picture of 99/99/9999 or > mm/dd/yyyy and 99/99/9999 or dd-mmm-yyyy and 99-xxx-9999. This format > allows total control over dates (at least in Western countries) ... I > am happy to donate the code... Windows (int the regional settings) follows > a similar approach. Well, this sounds interesting even if Windows *does* use the same technique ;) Certainly contributing the code could be useful. It could make its way into user contrib code, into special built-in formatting functions, or possibly into the backend as the default formatting mechanism. Without seeing the code and understanding the tradeoffs I can't predict which would be the most suitable, though in any case user contributed code is a great way to test out a new technique. If you want, post it raw or package it as user contributed code; either way, we'll look at it. TIA - Thomas -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California -
Re: [HACKERS] ISO dates with European Format
Theo Kramer <theo@flame.co.za> — 1999-09-14T17:08:51Z
Thomas Lockhart wrote: > Well, this sounds interesting even if Windows *does* use the same > technique ;) > > Certainly contributing the code could be useful. It could make its way > into user contrib code, into special built-in formatting functions, or > possibly into the backend as the default formatting mechanism. Without > seeing the code and understanding the tradeoffs I can't predict which > would be the most suitable, though in any case user contributed code > is a great way to test out a new technique. > > If you want, post it raw or package it as user contributed code; > either way, we'll look at it. TIA I'll rip it out, repackage it for postgres and send it off within the next week. -------- Regards Theo