pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [SQL] Ordering a date_part() query ...
13+ messages / 5 participants
[nested] [flat]

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 10:48  omid omoomi <oomoomi@hotmail.com>
  0 siblings, 0 replies; 13+ messages in thread

From: omid omoomi @ 2000-01-18 10:48 UTC (permalink / raw)
  To: scrappy@hub.org; +Cc: pgsql-sql

Hi,
Try some thng like this ( using order by clause ):
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
Date_part('day', stat_period ) , Date_part('hour',stat_period)  ;

Hope that helps,
regards.
Omid Omoomi


>From: The Hermit Hacker <scrappy@hub.org>
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Ordering a date_part() query ...
>Date: Tue, 18 Jan 2000 13:20:08 -0400 (AST)
>
>
>I have a query that looks like:
>
>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";
>
>
>Works great, except that the output looks like:
>
>Summary - by Day/Hour|impressions|click_thru
>---------------------+-----------+----------
>1/17/2000 23:00:00   |         63|         1
>1/18/2000 0:00:00    |        151|         0
>1/18/2000 10:00:00   |        348|         4
>1/18/2000 11:00:00   |        804|         1
>1/18/2000 12:00:00   |        180|         1
>1/18/2000 1:00:00    |         89|         0
>1/18/2000 2:00:00    |        112|         0
>1/18/2000 3:00:00    |        178|         0
>1/18/2000 4:00:00    |        184|         0
>1/18/2000 5:00:00    |        133|         0
>1/18/2000 6:00:00    |        119|         0
>1/18/2000 7:00:00    |        148|         0
>1/18/2000 8:00:00    |        224|         0
>1/18/2000 9:00:00    |        272|         0
>
>Notice the 10/11/12/etc hour stats come before the 1am stats?
>
>So its sorting okay on the date, but I need to get it to d it on the time
>as well..
>
>I'm still playing with it, but if someone can suggest a means of doing
>this, I'm all ears :)
>
>Thanks...
>
>Marc G. Fournier                   ICQ#7615664               IRC Nick: 
>Scrappy
>Systems Administrator @ hub.org
>primary: scrappy@hub.org           secondary: 
>scrappy@{freebsd|postgresql}.org
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 10:48  omid omoomi <oomoomi@hotmail.com>
  0 siblings, 0 replies; 13+ messages in thread

From: omid omoomi @ 2000-01-18 10:48 UTC (permalink / raw)
  To: scrappy@hub.org; +Cc: pgsql-sql

Hi,
Try some thing like this ( using order by clause ):
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
Date_part('day', stat_period ) , Date_part('hour',stat_period)  ;

Hope that helps,
regards.
Omid Omoomi


>From: The Hermit Hacker <scrappy@hub.org>
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Ordering a date_part() query ...
>Date: Tue, 18 Jan 2000 13:20:08 -0400 (AST)
>
>
>I have a query that looks like:
>
>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";
>
>
>Works great, except that the output looks like:
>
>Summary - by Day/Hour|impressions|click_thru
>---------------------+-----------+----------
>1/17/2000 23:00:00   |         63|         1
>1/18/2000 0:00:00    |        151|         0
>1/18/2000 10:00:00   |        348|         4
>1/18/2000 11:00:00   |        804|         1
>1/18/2000 12:00:00   |        180|         1
>1/18/2000 1:00:00    |         89|         0
>1/18/2000 2:00:00    |        112|         0
>1/18/2000 3:00:00    |        178|         0
>1/18/2000 4:00:00    |        184|         0
>1/18/2000 5:00:00    |        133|         0
>1/18/2000 6:00:00    |        119|         0
>1/18/2000 7:00:00    |        148|         0
>1/18/2000 8:00:00    |        224|         0
>1/18/2000 9:00:00    |        272|         0
>
>Notice the 10/11/12/etc hour stats come before the 1am stats?
>
>So its sorting okay on the date, but I need to get it to d it on the time
>as well..
>
>I'm still playing with it, but if someone can suggest a means of doing
>this, I'm all ears :)
>
>Thanks...
>
>Marc G. Fournier                   ICQ#7615664               IRC Nick: 
>Scrappy
>Systems Administrator @ hub.org
>primary: scrappy@hub.org           secondary: 
>scrappy@{freebsd|postgresql}.org
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Ordering a date_part() query ...
@ 2000-01-18 17:20  The Hermit Hacker <scrappy@hub.org>
  0 siblings, 3 replies; 13+ messages in thread

From: The Hermit Hacker @ 2000-01-18 17:20 UTC (permalink / raw)
  To: pgsql-sql


I have a query that looks like:

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";


Works great, except that the output looks like:

Summary - by Day/Hour|impressions|click_thru
---------------------+-----------+----------
1/17/2000 23:00:00   |         63|         1
1/18/2000 0:00:00    |        151|         0
1/18/2000 10:00:00   |        348|         4
1/18/2000 11:00:00   |        804|         1
1/18/2000 12:00:00   |        180|         1
1/18/2000 1:00:00    |         89|         0
1/18/2000 2:00:00    |        112|         0
1/18/2000 3:00:00    |        178|         0
1/18/2000 4:00:00    |        184|         0
1/18/2000 5:00:00    |        133|         0
1/18/2000 6:00:00    |        119|         0
1/18/2000 7:00:00    |        148|         0
1/18/2000 8:00:00    |        224|         0
1/18/2000 9:00:00    |        272|         0

Notice the 10/11/12/etc hour stats come before the 1am stats?

So its sorting okay on the date, but I need to get it to d it on the time
as well..

I'm still playing with it, but if someone can suggest a means of doing
this, I'm all ears :)

Thanks...

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




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 17:45  Mark Volpe <volpe.mark@epamail.epa.gov>
  parent: The Hermit Hacker <scrappy@hub.org>
  2 siblings, 1 reply; 13+ messages in thread

From: Mark Volpe @ 2000-01-18 17:45 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; pgsql-sql

The Hermit Hacker wrote:
> 
> I have a query that looks like:
> 
> 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";
> 

Try appending ORDER BY "Summary - by Day/Hour"::date
to your query.

Mark



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 18:26  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: The Hermit Hacker <scrappy@hub.org>
  2 siblings, 0 replies; 13+ messages in thread

From: Bruce Momjian @ 2000-01-18 18:26 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql

You may need to use CASE to output a column to be sorted on.


> 
> I have a query that looks like:
> 
> 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";
> 
> 
> Works great, except that the output looks like:
> 
> Summary - by Day/Hour|impressions|click_thru
> ---------------------+-----------+----------
> 1/17/2000 23:00:00   |         63|         1
> 1/18/2000 0:00:00    |        151|         0
> 1/18/2000 10:00:00   |        348|         4
> 1/18/2000 11:00:00   |        804|         1
> 1/18/2000 12:00:00   |        180|         1
> 1/18/2000 1:00:00    |         89|         0
> 1/18/2000 2:00:00    |        112|         0
> 1/18/2000 3:00:00    |        178|         0
> 1/18/2000 4:00:00    |        184|         0
> 1/18/2000 5:00:00    |        133|         0
> 1/18/2000 6:00:00    |        119|         0
> 1/18/2000 7:00:00    |        148|         0
> 1/18/2000 8:00:00    |        224|         0
> 1/18/2000 9:00:00    |        272|         0
> 
> Notice the 10/11/12/etc hour stats come before the 1am stats?
> 
> So its sorting okay on the date, but I need to get it to d it on the time
> as well..
> 
> I'm still playing with it, but if someone can suggest a means of doing
> this, I'm all ears :)
> 
> Thanks...
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org 
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 18:28  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Mark Volpe <volpe.mark@epamail.epa.gov>
  0 siblings, 0 replies; 13+ messages in thread

From: Bruce Momjian @ 2000-01-18 18:28 UTC (permalink / raw)
  To: Mark Volpe <volpe.mark@epamail.epa.gov>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql

> The Hermit Hacker wrote:
> > 
> > I have a query that looks like:
> > 
> > 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";
> > 
> 
> Try appending ORDER BY "Summary - by Day/Hour"::date
> to your query.

Oh, much better than my CASE idea.  I thought he was sorting date/time
strings, not datetime values.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 18:49  Mark Volpe <volpe.mark@epamail.epa.gov>
  parent: The Hermit Hacker <scrappy@hub.org>
  2 siblings, 2 replies; 13+ messages in thread

From: Mark Volpe @ 2000-01-18 18:49 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; pgsql-sql

INSERT INTO my_brain VALUES ('clue') :-)

Whoops, Bruce's response reminded me,
what I meant to say was
ORDER BY "Summary - by Day/Hour"::datetime

Mark

The Hermit Hacker wrote:
> 
> I have a query that looks like:
> 
> 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";
>



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 19:06  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Mark Volpe <volpe.mark@epamail.epa.gov>
  1 sibling, 0 replies; 13+ messages in thread

From: Bruce Momjian @ 2000-01-18 19:06 UTC (permalink / raw)
  To: Mark Volpe <volpe.mark@epamail.epa.gov>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql

> INSERT INTO my_brain VALUES ('clue') :-)
> 
> Whoops, Bruce's response reminded me,
> what I meant to say was
> ORDER BY "Summary - by Day/Hour"::datetime

Yea, I was right.  He is constructing a string here.

> 
> Mark
> 
> The Hermit Hacker wrote:
> > 
> > I have a query that looks like:
> > 
> > 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";
> >
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 19:16  The Hermit Hacker <scrappy@hub.org>
  parent: Mark Volpe <volpe.mark@epamail.epa.gov>
  1 sibling, 1 reply; 13+ messages in thread

From: The Hermit Hacker @ 2000-01-18 19:16 UTC (permalink / raw)
  To: Mark Volpe <volpe.mark@epamail.epa.gov>; +Cc: pgsql-sql


Had thought about that one:

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;

results in:

ERROR:  attribute 'Summary - by Day/Hour' not found

But, that gave me an idea...

SELECT ( date_part('month', stat_period) || '/' ||
         date_part('day', stat_period)   || '/' ||
         date_part('year', stat_period)  || ' ' ||
         date_part('hour', stat_period)  || ':00:00' )::datetime as day,
       sum(impressions) as impressions, sum(click_thru) as click_thru
  FROM banner_count
GROUP BY day;


CASTing the results gives me what I want, and gives cleaner lookign
results then what I had :)


Summary - by Day/Hour       |impressions|click_thru
----------------------------+-----------+----------
Mon Jan 17 23:00:00 2000 EST|         63|         1
Tue Jan 18 00:00:00 2000 EST|        151|         0
Tue Jan 18 01:00:00 2000 EST|         89|         0
Tue Jan 18 02:00:00 2000 EST|        112|         0
Tue Jan 18 03:00:00 2000 EST|        178|         0
Tue Jan 18 04:00:00 2000 EST|        184|         0
Tue Jan 18 05:00:00 2000 EST|        133|         0
Tue Jan 18 06:00:00 2000 EST|        119|         0
Tue Jan 18 07:00:00 2000 EST|        148|         0
Tue Jan 18 08:00:00 2000 EST|        224|         0
Tue Jan 18 09:00:00 2000 EST|        272|         0
Tue Jan 18 10:00:00 2000 EST|        348|         4
Tue Jan 18 11:00:00 2000 EST|        804|         1
Tue Jan 18 12:00:00 2000 EST|        632|         1
Tue Jan 18 13:00:00 2000 EST|       1200|         0
Tue Jan 18 14:00:00 2000 EST|        110|         0


On Tue, 18 Jan 2000, Mark Volpe wrote:

> INSERT INTO my_brain VALUES ('clue') :-)
> 
> Whoops, Bruce's response reminded me,
> what I meant to say was
> ORDER BY "Summary - by Day/Hour"::datetime
> 
> Mark
> 
> The Hermit Hacker wrote:
> > 
> > I have a query that looks like:
> > 
> > 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";
> >
> 

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




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 19:45  Tom Lane <tgl@sss.pgh.pa.us>
  parent: The Hermit Hacker <scrappy@hub.org>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2000-01-18 19:45 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql

The Hermit Hacker <scrappy@hub.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?

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...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 21:06  The Hermit Hacker <scrappy@hub.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: The Hermit Hacker @ 2000-01-18 21:06 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

On Tue, 18 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.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@hub.org           secondary: scrappy@{freebsd|postgresql}.org 




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 22:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: The Hermit Hacker <scrappy@hub.org>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2000-01-18 22:26 UTC (permalink / raw)
  To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql

The Hermit Hacker <scrappy@hub.org> writes:
>> 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 ...

Oh, so "stat_period" is not a period label but an exact timestamp,
and you want to group into hourly blocks?  OK, then group/order by
date_trunc("hour", stat_period) is probably what you want...

I'd suggest choosing another name for the field, too ;-)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [SQL] Ordering a date_part() query ...
@ 2000-01-18 22:43  The Hermit Hacker <scrappy@hub.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 13+ messages in thread

From: The Hermit Hacker @ 2000-01-18 22:43 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

On Tue, 18 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> >> 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 ...
> 
> Oh, so "stat_period" is not a period label but an exact timestamp,
> and you want to group into hourly blocks?  OK, then group/order by
> date_trunc("hour", stat_period) is probably what you want...

good, ya had me worried I missed something in the docs on this one :)

> I'd suggest choosing another name for the field, too ;-)

*grin* I'm the only one that accesses it, so it doesn't much matter 

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




^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2000-01-18 22:43 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-18 10:48 Re: [SQL] Ordering a date_part() query ... omid omoomi <oomoomi@hotmail.com>
2000-01-18 10:48 Re: [SQL] Ordering a date_part() query ... omid omoomi <oomoomi@hotmail.com>
2000-01-18 17:20 Ordering a date_part() query ... The Hermit Hacker <scrappy@hub.org>
2000-01-18 17:45 ` Mark Volpe <volpe.mark@epamail.epa.gov>
2000-01-18 18:28   ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-18 18:26 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-18 18:49 ` Mark Volpe <volpe.mark@epamail.epa.gov>
2000-01-18 19:06   ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-18 19:16   ` The Hermit Hacker <scrappy@hub.org>
2000-01-18 19:45     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-18 21:06       ` The Hermit Hacker <scrappy@hub.org>
2000-01-18 22:26         ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-18 22:43           ` The Hermit Hacker <scrappy@hub.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox