p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC; to_char | count
---------+------- Wed | 1447 Tue | 618 Thu | 1161 Sun | 230 Sat | 362 Mon | 760 Fri
| 1281
(7 rows)
The problem is that I want those results sorted in day of week order,
not text order of the day name, so I tried this:
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM sclog WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'D') DESC;
ERROR: column "sclog.logtime" must appear in the GROUP BY clause or be
used in an aggregate function
Now obviously I don't want to group by logtime (a timestamp) so how do I
work around this? What I really need is a function that converts from
the char representation to a day of week number or vice versa. I also
have the same problem with month names.
