Обсуждение: ISO dates with European Format
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
> 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
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
> > 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
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
> 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
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