pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate arithmetic over calender year boundaries
2+ messages / 2 participants
[nested] [flat]
* date arithmetic over calender year boundaries
@ 2004-04-28 04:12 Ray Jackson <rmj@geography.otago.ac.nz>
0 siblings, 1 reply; 2+ messages in thread
From: Ray Jackson @ 2004-04-28 04:12 UTC (permalink / raw)
To: pgsql-sql
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
+----------------------+------------------------------------------+
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: date arithmetic over calender year boundaries
@ 2004-04-28 06:48 Bruno Wolff III <bruno@wolff.to>
parent: Ray Jackson <rmj@geography.otago.ac.nz>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2004-04-28 06:48 UTC (permalink / raw)
To: Ray Jackson <rmj@geography.otago.ac.nz>; +Cc: pgsql-sql
On Wed, Apr 28, 2004 at 16:12:34 +1200,
Ray Jackson <rmj@geography.otago.ac.nz> wrote:
> 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.
It isn't completely clear what you want to do, but you might be able to
use date_part in a where clause to limit the data used for each year
if the range within each year is the same.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-28 06:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-28 04:12 date arithmetic over calender year boundaries Ray Jackson <rmj@geography.otago.ac.nz>
2004-04-28 06:48 ` Bruno Wolff III <bruno@wolff.to>
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