Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Ordering a date_part() query ...

From: The Hermit Hacker <scrappy(at)hub(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] Ordering a date_part() query ...
Date: 2000-01-18 21:06:38
Message-ID: Pine.BSF.4.21.0001181702520.23487-100000@thelab.hub.org (view raw or flat)
Thread:
Lists: pgsql-sql
On Tue, 18 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > SELECT ( date_part('month', stat_period) || '/' ||
> >          date_part('day', stat_period)   || '/' ||
> >          date_part('year', stat_period)  || ' ' ||
> >          date_part('hour', stat_period)  || ':00:00' ) as "Summary - by Day/Hour",
> >        sum(impressions) as impressions, sum(click_thru) as click_thru
> >   FROM banner_count
> > GROUP BY "Summary - by Day/Hour"
> > ORDER BY "Summary - by Day/Hour"::datetime;
> 
> 
> Uh, why don't you just GROUP BY and ORDER BY stat_period?

Okay, maybe I'm misunderstanding GROUP BY, but my understanding is that it
groups "like" values ... my above example would give me a count of all
impressions received over a one hour period ...

To use stat_period, I'd get one row for each 'stat_period' value, which,
since its in seconds, would mean every record in the table...

The above takes off the min/secs and then I group by hours ...

> I also wonder whether you can't find a combination of date_trunc and
> datestyle that will produce the output format you want.  Doing it
> like the above sure seems like using the wrong tool for the job...

It is quite possible ... I'm not used to using functions much so far,
first time I've really had a use for it...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy(at)hub(dot)org           secondary: scrappy(at){freebsd|postgresql}.org 


In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2000-01-18 22:26:37
Subject: Re: [SQL] Ordering a date_part() query ...
Previous:From: Tom LaneDate: 2000-01-18 19:45:10
Subject: Re: [SQL] Ordering a date_part() query ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group