Re: query help - Mailing list pgsql-general

From Josh Tolley
Subject Re: query help
Date
Msg-id e7e0a2570708142020q746dc52ald7b068ec3479e012@mail.gmail.com
Whole thread Raw
In response to query help  (Kirk Wythers <kwythers@umn.edu>)
List pgsql-general
On 8/14/07, Kirk Wythers <kwythers@umn.edu> wrote:
>
> I need some help with rewriting a query. I have a fairly complicated query
> (for me anyway) that dumps daily climate data, filling in missing data with
> monthly averages (one line per day).
>
> I want to output monthly averages (one line per month). I am having a hard
> time wrapping my head around this. Particularly how to deal with the doy
> column (day of year). I have tried several approaches and my forehead is
> starting to get my keyboard bloody.

I think this came up on IRC today, so perhaps this is only for the
archives' sake, but you want to do something like this:

Assuming you have a table as follows:

CREATE TABLE climate_data (
   measurement_time  timestamp,
   measurement_value  integer);

...and you insert data into it regularly, you can get the average
measurement over a period of time with date_trunc(), which will
truncate a date or timestamp value to match whatever precision you
specify. For example, see the following:

eggyknap=# select date_trunc('month', now());
       date_trunc
------------------------
 2007-08-01 00:00:00-06
(1 row)

Note: the -06 at the end means I'm in mountain time.

So if you want to get the average measurement over a month's time, you
need to do something like this:

SELECT DATE_TRUNC('MONTH', measurement_time), AVG(measurement_value)
FROM climate_data GROUP BY DATE_TRUNC('MONTH', measurement_time);

This will chop all the measurement_time values down to the month the
measurement was taken in, put all measurements in groups based on the
resulting value, and take the average measurement_value from each
group.

- Josh

pgsql-general by date:

Previous
From: "Ron Olson"
Date:
Subject: Blobs in Postgresql
Next
From: "Scott Marlowe"
Date:
Subject: Re: Moving to postgresql and some ignorant questions