Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 051DE2E2E71 for ; Thu, 6 Mar 2008 20:18:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 32927-02-3 for ; Thu, 6 Mar 2008 20:18:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.internode.com.au (mail.internode.com.au [192.83.231.27]) by postgresql.org (Postfix) with ESMTP id 352F12E2DB8 for ; Thu, 6 Mar 2008 20:18:20 -0400 (AST) Received: from [172.16.0.221] (staffnet.internode.com.au [203.26.95.65]) by mail.internode.com.au (8.13.7/8.12.1) with ESMTP id m270IC0W002716; Fri, 7 Mar 2008 10:48:12 +1030 (CST) Message-ID: <47D089CE.4040607@internode.com.au> Date: Fri, 07 Mar 2008 10:48:22 +1030 From: Shilpa Sudhakar User-Agent: Thunderbird 1.5.0.14 (Windows/20071210) MIME-Version: 1.0 To: cat@zip.com.au CC: Tom Lane , pgsql-admin@postgresql.org Subject: Re: Time Zone in Postgres References: <2d6659d70803051856x652174a5ic8655d070a95db59@mail.gmail.com> <2d6659d70803052151q5861981aw3a1a2e39e3970704@mail.gmail.com> <19281.1204827832@sss.pgh.pa.us> <47D076B3.8090304@internode.com.au> <20080306234653.GA20591@bunyip.billabong.weebeasite.net> In-Reply-To: <20080306234653.GA20591@bunyip.billabong.weebeasite.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.91.2, clamav-milter version 0.91.2 on mail X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/57 X-Sequence-Number: 28540 Hi Cat, As per my previous mail, i've done few checks on the postgres timezone files but not sure if am looking at the right file. I'll paste the details again below . 1) Checked the *postgresql.conf file* and the *timezone *parameter is set as "*unknown*". 2) */local/pkgsrc/databases/postgresql80-pgcrypto/work/postgresql-8.0.9/src/timezone/data $ grep 2007 australasia* Rule AS 2007 max - Mar lastSun 2:00s 0 - Rule AT 2007 max - Mar lastSun 2:00s 0 - Rule AV 2007 max - Mar lastSun 2:00s 0 - Rule AN 2007 max - Mar lastSun 2:00s 0 - Rule LH 2007 max - Mar lastSun 2:00 0 - */local/pkgsrc/databases/postgresql80-pgcrypto/work/postgresql-8.0.9/src/timezone/data $ grep 2008 australasia* No results From 2008, the DST ends on first Sunday of April and not on the last Sun of March. Thanks cat@zip.com.au wrote: > On Fri, Mar 07, 2008 at 09:26:51AM +1030, Shilpa Sudhakar wrote: > >> Hi Tom, >> >> We have different databases with different versions. >> >> I checked the database with *version 8.2.4* by running the below query >> >> echo select timestamp with time zone \'epoch\' + 1206970200 \* INTERVAL >> \'1 second\'\; | psql template1 >> ?column? >> --------------------------- >> 2008-03-31 23:00:00+09:30 >> (1 row) >> >> This shows the wrong date. The actual result should be *2008-04-01 >> 00:00:00+10:30 * >> > > I believe postgres comes with its own timezone info. Check if the stuff > it has has the right timezone information. Personally I get it to use > the system timezone definitions so that I can keep things upto-date > easier. > >