X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1E000D1B519 for ; Wed, 28 Apr 2004 01:12:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 55604-09 for ; Wed, 28 Apr 2004 01:12:38 -0300 (ADT) Received: from mailhub1.otago.ac.nz (mailhub1.otago.ac.nz [139.80.64.218]) by svr1.postgresql.org (Postfix) with ESMTP id EEE4ED1B50C for ; Wed, 28 Apr 2004 01:12:36 -0300 (ADT) Received: (from root@localhost) by mailhub1.otago.ac.nz (8.12.11/8.12.11) id i3S4CZLC013947 for pgsql-sql@postgresql.org; Wed, 28 Apr 2004 16:12:35 +1200 Received: from ungol.otago.ac.nz (ungol.otago.ac.nz [139.80.64.85]) by mailhub1.otago.ac.nz (8.12.11/8.12.9) with ESMTP id i3S4CY4K013881 for ; Wed, 28 Apr 2004 16:12:34 +1200 Received: (from apache@localhost) by ungol.otago.ac.nz (8.11.6/8.11.6) id i3S4CYF24169 for pgsql-sql@postgresql.org; Wed, 28 Apr 2004 16:12:34 +1200 X-Authentication-Warning: ungol.otago.ac.nz: apache set sender to rmj@geography.otago.ac.nz using -f Received: from atelija.otago.ac.nz (atelija.otago.ac.nz [139.80.28.120]) by webmail.geography.otago.ac.nz (IMP) with HTTP for ; Wed, 28 Apr 2004 16:12:34 +1200 Message-ID: <1083125554.408f2f328abed@webmail.geography.otago.ac.nz> Date: Wed, 28 Apr 2004 16:12:34 +1200 From: Ray Jackson To: pgsql-sql@postgresql.org Subject: date arithmetic over calender year boundaries MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.3-cvs X-Originating-IP: 139.80.28.120 X-scanner: scanned by Inflex 1.0.12.7 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/307 X-Sequence-Number: 17682 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 +----------------------+------------------------------------------+