(back on list)
> >> > [...] It seems that the sorting order on dates is incorrect under
> >> > certain circumstances: [...] set DateStyle to 'German'; (does
> >> > not have an effect on the problem) [...]
> >> From the examples you gave it appears that you are still using
> >> American date format (YYYY-MM-DD)
> >An irrelevant detail: the YYYY-MM-DD date format is ISO-8601, and is
> >not particularly common in America. The US style looks like
> >MM/DD/YYYY.
> >I think your analysis and recommendations are correct; the sort order
> >in the example looked good to me too...
> unfortunately, the sort order is indeed *not* correct.
<snip>
I think we have different ideas on what "correct" means. In my case,
"correct" means that the result looks as I would expect, and I
implemented the code. You apparently want something different, or are
pointing out a problem that I'm simply not seeing despite your best
efforts :)
> These are transaction dates which have taken place at the beginning of
> each months.
Are you saying that these dates should be on the first of each month?
That is not what you are entering into the table...
You did not describe the schema or the version of Postgres you are
using. Is there a (minor) time zone rotation you are seeing which could
be ascribed to a know problem in the "date" data type in pre-v6.4.2 +
patches?
> BTW:ida=> show datestyle;
> NOTICE: DateStyle is ISO with US (NonEuropean) conventions
The US or European conventions affect *input* values when the DateStyle
is ISO but the actual input is in some other convention (e.g. "US" or
"European" traditional Postgres or SQL formats). It does not affect the
output values. Please look at the chapter on data types in the "new"
documentation for more details.
> The problem seems to be that under certain circumstances the datestyle
> is not used:
> as can be seen the month and day columns are switched. The sorting is
> ok in the latter case but not in the first.
> It seems to me that we still do have a problem.
Please read the docs on the date/time data types. I believe that the
examples and descriptions there will help. As I've indicated, I think
the result you are seeing is as I would expect it to be for an ISO-8601
format, which is what is implemented.
Regards.
- Tom
