Thread: Bug in sortin routine?
Hello everyone,
I am not sure if this is the appropriate mailing lists. But lets try
anyway.
It seems that the sorting order on dates is incorrect under certain
circumstances:
The query :
select tdat
from quick
where empfang like 'HUK-COBURG VERS.-GRUPPE' and
zweck like 'BEITRAG F. 300/338958-M PFLEGEPFLICHTVERSICHERUNG'
and date_part ('year',tdat::datetime)=1997
order by tdat;
produces:
tdat
----------
1997-01-04
1997-01-08
1997-01-09
1997-01-10
1997-01-12
1997-02-01
1997-02-05
1997-02-06
1997-02-07
1997-03-02
1997-03-03
1997-03-11
(12 rows)
while this gives the correct sort order:
select tdat
from quick
order by tdat;
1997-11-26
1997-11-27
1997-11-27
1997-11-27
1997-11-28
1997-11-28
1997-11-28
1997-12-03
1997-12-05
1997-12-05 (just a section)
any idea of what may be going on?
set DateStyle to 'German'; (does not have an effect on the problem)
postgresql-6.4.2-2
readhat 5.2
greetings
Eildert Groeneveld
=========================================
Institute for
Animal Science and Animal Behaviour
Mariensee 31535 Neustadt Germany
Tel : (49)(0)5034 871155
Fax : (49)(0)5034 92579
www : http://www.tzv.fal.de/~eg/
e-mail: eg@tzv.fal.de
=========================================
Eilert Groeneveld <eg@tzv.fal.de> wrote:
> [...] 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); if that is true, the lines are
ordered as they should be. If some of the date values appear to be
"correct" after manual/visual control and others not, you maybe have
inserted some of the values with American, others with German format
in use, resulting in a messed up table (and you'll have to fix it by
hand). Are you sure you've really set DateStyle correctly? I have
something like
PGDATESTYLE=German
export PGDATESTYLE
in my shell's .bashrc, and selecting some date values from one of my
tables gives e.g.
datum
----------
16.08.1996
17.08.1996
18.08.1996
19.08.1996
20.08.1996
that is, DD.MM.YYYY (for testing, dates with 'day' after the 12th of
the month are better suited than the others ;-). If you set
PGDATESTYLE like I did, are you sure the configuration file got
source'd? You can test the state of DateStyle with
xxx=> show datestyle;
NOTICE: DateStyle is German with European conventions
SHOW VARIABLE
from inside psql.
Hope it helps ...
viele Gr��e,
Ulf
--
======================================================================
Ulf Mehlig <umehlig@zmt.uni-bremen.de>
Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------
> > [...] 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...
- Tom
(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