Re: timestamp group by bug???

On Mon, 21 Mar 2005 12:56:52 -0500, Tom Lane wrote
> "Celia McInnis" <celia(at)drmath(dot)ca> writes:
> > Help - I'm not sure if this is a bug, but I wouldn't call it a feature! :-
)
>
> Hardly a bug ... you told it to order by a textual value, why
> would you expect a non-alphabetical sort ordering?

I do since the "D" option gives a single digit day of week number (ie., text
sort would give identical ordering to text sort).

>
> I would suggest ordering by EXTRACT(DOW FROM mytimestamp).
> You'll probably have to list that as a second GROUP BY item in order
> to make Postgres happy with the query.
>
> regards, tom lane

Thanks, Tom - the above statement is what pointed me in the correct
direction - that I needed to group by BOTH the name of the day and the number
of the day. Whether that number was expressed as a text field (as I did) or
as a numeical value (as you suggested) was irrelevant.

For example the following works just fine:

SELECT to_char(mytimestamp,'D'),to_char(mytimestamp,'DY'),COUNT(*) FROM
mytable GROUP BY to_char(mytimestamp,'D'),to_char(mytimestamp,'DY') ORDER BY
to_char(mytimestamp,'D');

rather than my original error:

SELECT to_char(mytimestamp,'D'),to_char(mytimestamp,'DY'),COUNT(*) FROM
mytable GROUP BY to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');

By the way, as a novice, I am/was a little surprised at HAVING to do this,
since both things in the grouping are just simple functions of the same
underlying table variable mytimestamp.

It is also worth noting that some other databases do not require this double
grouping (though experiments show that they mess up in other ways concerning
groupings and orderings, so maybe they should have done as postgres has!) -
notably, the following works in mysql:

select date_format(mytimestamp,'%w'),date_format(mytimestamp,'%W'),count(*)
from mytable group by date_format(mytimestamp,'%w') order by date_format
(mytimestamp,'%w);

Thanks very much, Celia McInnis