Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F038A2E2ECC for ; Thu, 6 Mar 2008 20:19:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 88641-03 for ; Thu, 6 Mar 2008 20:19:47 -0400 (AST) X-Greylist: delayed 00:28:49.292022 by SQLgrey-1.7.5 Received: from bunyip.billabong.weebeasite.net (CPE-124-189-48-61.nsw.bigpond.net.au [124.189.48.61]) by postgresql.org (Postfix) with ESMTP id 402C32E2DFD for ; Thu, 6 Mar 2008 20:19:46 -0400 (AST) Received: from hogarth by bunyip.billabong.weebeasite.net with local (Exim 4.63) (envelope-from ) id 1JXPnZ-0005XI-VL; Fri, 07 Mar 2008 10:46:54 +1100 Date: Fri, 7 Mar 2008 10:46:53 +1100 From: cat@zip.com.au To: Shilpa Sudhakar Cc: Tom Lane , pgsql-admin@postgresql.org Subject: Re: Time Zone in Postgres Message-ID: <20080306234653.GA20591@bunyip.billabong.weebeasite.net> References: <2d6659d70803051856x652174a5ic8655d070a95db59@mail.gmail.com> <2d6659d70803052151q5861981aw3a1a2e39e3970704@mail.gmail.com> <19281.1204827832@sss.pgh.pa.us> <47D076B3.8090304@internode.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <47D076B3.8090304@internode.com.au> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_DYNAMIC=0.1 X-Spam-Level: X-Archive-Number: 200803/58 X-Sequence-Number: 28541 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. -- CaT