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

date arithmetic over calender year boundaries

From: Ray Jackson <rmj(at)geography(dot)otago(dot)ac(dot)nz>
To: pgsql-sql(at)postgresql(dot)org
Subject: date arithmetic over calender year boundaries
Date: 2004-04-28 04:12:34
Message-ID: 1083125554.408f2f328abed@webmail.geography.otago.ac.nz (view raw or flat)
Thread:
Lists: 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(at)geography(dot)otago(dot)ac(dot)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
+----------------------+------------------------------------------+


Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-04-28 04:16:28
Subject: Re: Which SQL command creates ExclusiveLock?
Previous:From: Tony ReinaDate: 2004-04-27 19:12:51
Subject: SELECTing part of a matrix

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