Received: from mta2-rme.xtra.co.nz (mta2-rme.xtra.co.nz [203.96.92.3]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0SAfaq62211 for ; Sun, 28 Jan 2001 05:41:37 -0500 (EST) (envelope-from csawtell@xtra.co.nz) Received: from berty ([210.55.151.216]) by mta2-rme.xtra.co.nz with SMTP id <20010128104132.CRXX13182994.mta2-rme.xtra.co.nz@berty>; Sun, 28 Jan 2001 23:41:32 +1300 Content-Type: text/plain; charset="iso-8859-1" From: Christopher Sawtell Organization: At Home To: "Glen and Rosanne Eustace" , Subject: Re: Re: Problem with Dates Date: Sun, 28 Jan 2001 23:47:18 +1300 X-Mailer: KMail [version 1.2] References: <00ff01c0863e$c4ef8620$0101a8c0@godzone.net.nz> <20010124181731.G17481@thune.yy.com> <00d401c08729$077cc440$0101a8c0@godzone.net.nz> In-Reply-To: <00d401c08729$077cc440$0101a8c0@godzone.net.nz> MIME-Version: 1.0 Message-Id: <01012823471700.24525@berty> Content-Transfer-Encoding: 8bit X-Archive-Number: 200101/425 X-Sequence-Number: 1101 On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote: > template1=# select '31/12/2000'::date + '365 days'::timespan; > ?column? > ------------------------ > 2002-01-01 00:00:00+13 <<<<<<<<<<< Wrong > (1 row) This appears to be fixed in the current sources by CVSup :- 23:16:03 chris@berty:~ $ psql --version psql (PostgreSQL) 7.1beta3 contains readline, history support Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. Winter Time: template1=# select '30/06/2000:10:30'::timestamp as date; date ------------------------ 2000-06-30 10:30:00+12 (1 row) template1=# select '30/06/2000:10:30'::timestamp + '1 year'::interval as date; date ------------------------ 2001-06-30 10:30:00+12 (1 row) Correct!! Summer Time: template1=# select '30/12/2000:10:30'::timestamp as date; date ------------------------ 2000-12-30 10:30:00+13 (1 row) template1=# select '30/12/2000:10:30'::timestamp + '1 year'::interval as date; date ------------------------ 2001-12-30 10:30:00+13 (1 row) Correct!! Now lets try with '365 days' instead of '1 year' template1=# select '30/12/2000:10:30'::timestamp + '365 days'::interval as date; date ------------------------ 2001-12-30 10:30:00+13 (1 row) Correct!! Now lets try across a leap year: template1=# select '30/01/2004:10:30'::timestamp as date; date ------------------------ 2004-01-30 10:30:00+13 (1 row) template1=# select '30/01/2004:10:30'::timestamp + '1 year'::interval as date; date ------------------------ 2005-01-30 10:30:00+13 (1 row) template1=# select '30/01/2004:10:30'::timestamp + '365 days'::interval as date; date ------------------------ 2005-01-29 10:30:00+13 (1 row) Correct!! school=# select * from day; number | name --------+----------- 0 | Sunday 1 | Monday 2 | Tuesday 3 | Wednesday 4 | Thursday 5 | Friday 6 | Saturday (7 rows) school=# select name from day where number= \ (select date_part('dow','now'::datetime) as day); name -------- Sunday (1 row) Correct!! The PostgreSQL Team is to be congratulated. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN 45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--