From: Ray Jackson <rmj@geography.otago.ac.nz>
To: pgsql-sql@postgresql.org
Subject: date arithmetic over calender year boundaries
Date: Wed, 28 Apr 2004 16:12:34 +1200
Message-ID: <1083125554.408f2f328abed@webmail.geography.otago.ac.nz> (raw)
Hi,
The following Postgres 7.1 query extracts aggregated data for an
arbitrary period within each year for sites in a table containing
30 years of temperature data.
topo=> \d longterm
Table "longterm"
Attribute | Type | Modifier
-----------+--------------+----------
site | character(5) | not null
obs | date | not null
lo | numeric(3,1) |
hi | numeric(3,1) |
topo=> select site, extract(year from obs) as year, sum((hi+lo)/2-4) as
gdd4
topo=> from temperature
topo=> where extract(doy from obs) >= 1
topo=> and extract(doy from obs) <= 5
topo=> group by site, extract(year from obs);
site | year | gdd4
-------+------+--------
01001 | 1973 | 51.7
01001 | 1974 | 39.5
01001 | 1975 | 67.9
. . .
. . .
My question is, how can this type of query be contructed to do
the same sort of thing for a period that straddles the calendar year
boundary?
--
Regards,
+----------------------+------------------------------------------+
Ray Jackson email: rmj@geography.otago.ac.nz
Computing Coordinator phone: +64-3-479-8768
Dept. Geography/Te Ihowhenua fax: +64-3-479-8706
Otago University postal: Box 56, Dunedin
Te Whare Wananga o Otago AOTEAROA/NEW ZEALAND
+----------------------+------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rmj@geography.otago.ac.nz
Subject: Re: date arithmetic over calender year boundaries
In-Reply-To: <1083125554.408f2f328abed@webmail.geography.otago.ac.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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