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

Re: Time Aggregates

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: Itai Zukerman <zukerman(at)math-hat(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Time Aggregates
Date: 2000-08-02 15:27:13
Message-ID: 39883DD1.610A0954@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-sql
> I'm currently doing this:
>   SELECT symbol, date_trunc('minute', posted),
>          min(price), max(price), avg(price)
>   FROM trade
>   GROUP BY symbol, date_trunc('minute', posted);
> to get a list of minute-averages of trade prices.  I get the feeling
> that this is bad form, that I should be doing this some other way.  Is
> that the case?

Looks OK to me. If you are doing this *a lot* (i.e. many more queries
than inserts), then you might want to set up another column which
contains date_trunc('minute',posted) to avoid the calculation. Something
like

  create table trade (
    symbol text,
    posted timestamp,
    price integer,
    mpost  timestamp
  )

then define a rule to update mpost when posted gets set (haven't done
that part).

                    - Thomas

In response to

Responses

pgsql-sql by date

Next:From: David Lloyd-JonesDate: 2000-08-02 15:37:44
Subject: Re: Time Aggregates
Previous:From: Itai ZukermanDate: 2000-08-02 13:41:52
Subject: Time Aggregates

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