| From: | Mike Nolan <nolan(at)gw(dot)tssi(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org (pgsql general list) |
| Subject: | Select/Group by/Order by question |
| Date: | 2004-04-08 23:49:23 |
| Message-ID: | 200404082349.i38NnN45017008@gw.tssi.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I'm trying to create a summary log by hour. Here's the query (somewhat
simplified):
select to_char(mtrantime,'mm-dd hh AM') as datetime,
count(*) as tot from memtran
group by datetime
order by datetime;
The problem is this produces the data in the following order:
datetime | tot
-------------+-----
04-08 01 PM | 14
04-08 02 PM | 15
04-08 03 PM | 23
04-08 07 AM | 8
04-08 08 AM | 54
04-08 09 AM | 30
04-08 10 AM | 11
04-08 11 AM | 10
04-08 11 PM | 7
04-08 12 PM | 10
What I'd really like is to get it in chronological order by hour:
04-08 07 AM | 8
04-08 08 AM | 54
04-08 09 AM | 30
04-08 10 AM | 11
04-08 11 AM | 10
04-08 12 PM | 10
04-08 01 PM | 14
04-08 02 PM | 15
04-08 03 PM | 23
04-08 11 PM | 7
I would prefer not to show the time of day in 24 hour format, but
there doesn't appear to be a way to order by something that
isn't in the select and group by clause and I don't want to display
the hour twice.
Putting the AM/PM before the HH (which looks a bit clumsy) almost works,
except that 12PM gets sorted to the bottom after 11PM.
Is there an easy way around this?
--
Mike Nolan
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jim Seymour | 2004-04-09 00:23:51 | Re: pg_ctl problem |
| Previous Message | Patrick Hatcher | 2004-04-08 22:51:13 | Upgrading from 7.3.6 to 7.4.2 pg_dumpall question |