Thread: date arithmetic over calender year boundaries

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 nullobs       | date         | not nulllo        |
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.701001 | 1974 |   39.501001 | 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.nzComputingCoordinator          phone:   +64-3-479-8768Dept. Geography/Te Ihowhenua   fax:
+64-3-479-8706OtagoUniversity               postal:  Box 56, DunedinTe Whare Wananga o Otago       AOTEAROA/NEW
ZEALAND
+----------------------+------------------------------------------+

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.