Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A686F2E004E for ; Thu, 6 Mar 2008 02:45:45 -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 10888-07 for ; Thu, 6 Mar 2008 02:45:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from blu139-omc1-s21.blu139.hotmail.com (blu139-omc1-s21.blu139.hotmail.com [65.55.175.161]) by postgresql.org (Postfix) with ESMTP id A717C2E007E for ; Thu, 6 Mar 2008 02:45:42 -0400 (AST) Received: from BLU110-W58 ([65.55.162.181]) by blu139-omc1-s21.blu139.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 5 Mar 2008 22:45:42 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_589b3967-e077-404f-b52e-09ca4a4218a7_" X-Originating-IP: [59.160.193.34] From: Vishal Arora To: Shilpa , Subject: Time Zone in Postgres Date: Thu, 6 Mar 2008 12:15:41 +0530 Importance: Normal In-Reply-To: <2d6659d70803052151q5861981aw3a1a2e39e3970704@mail.gmail.com> References: <2d6659d70803051856x652174a5ic8655d070a95db59@mail.gmail.com> <2d6659d70803052151q5861981aw3a1a2e39e3970704@mail.gmail.com> MIME-Version: 1.0 X-OriginalArrivalTime: 06 Mar 2008 06:45:42.0103 (UTC) FILETIME=[B256A670:01C87F55] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.38 tagged_above=0 required=5 tests=AWL=-0.227, HTML_FONT_FACE_BAD=0.606, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/48 X-Sequence-Number: 28531 --_589b3967-e077-404f-b52e-09ca4a4218a7_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I've been working on a timezone issue. I am in Adelaide Australia and t= he daylight savings time this year has been changed. I am trying to figure = out the file which is required to be changed. I've done the below research:= 1) Checked the postgresql.conf file and the timezone parameter is set as "u= nknown". According to the documentation, if this parameter is set to unknow= n then postgresql will first look at the TZ parameter, if the TZ parameter = doesn't exist then it looks at the system time. This means that postgres sh= ould look at the OS level time but am confused with varied results from dif= ferent queries as explained below. 2) I ran the below query in 2 different = postgresql databases and found that both show different resultsServer 1:ech= o select timestamp with time zone \'epoch\' + 1206970200 \* INTERVAL \'1 se= cond\'\; | psql template1 ?column? --------------------------- 20= 08-03-31 23:00:00+09:30 (1 row) Server2: echo select timestamp with time zo= ne \'epoch\' + 1206970200 \* INTERVAL \'1 second\'\; | psql template1 = ?column? --------------------------- 2008-04-01 00:00:00+10:30 (1 row)= Server 2 is right.3) I ran the below command:/local/pkg/share/postgresql/t= imezone/Australia $ /usr/sbin/zdump -v Australia/Adelaide | grep 2007Austra= lia/Adelaide Sat Mar 24 16:29:59 2007 UTC =3D Sun Mar 25 02:59:59 2007 CST= isdst=3D1Australia/Adelaide Sat Mar 24 16:30:00 2007 UTC =3D Sun Mar 25 0= 2:00:00 2007 CST isdst=3D0Australia/Adelaide Sat Oct 27 16:29:59 2007 UTC = =3D Sun Oct 28 01:59:59 2007 CST isdst=3D0Australia/Adelaide Sat Oct 27 16= :30:00 2007 UTC =3D Sun Oct 28 03:00:00 2007 CST isdst=3D1Is this above det= ail from OS level or from Postgres Level ?4) I tried finding the timezone f= iles and found them under /local/pkg/share/postgresql/timezone/AustraliaI t= ried opening the Adelaide file under this folder but the file is not readab= le. Can you please let me know if i need to change the settings in this fil= e or in someother file. I am not able to find good documentation on this. 5= ) One more query: /local/pkgsrc/databases/postgresql80-pgcrypto/work/postgr= esql-8.0.9/src/timezone/data $ grep 2007 australasiaRule AS 2007 = max - Mar lastSun 2:00s 0 -Rule AT 2007 m= ax - 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/postgresq= l80-pgcrypto/work/postgresql-8.0.9/src/timezone/data $ grep 2008 australasi= aNo resultsBasically, i want to know from where does postgresql get the tim= e and where should i modify the DST settings. Thanks and Regards,Shilpa Live the life in style with MSN Lifestyle. Check out! Try it now! _________________________________________________________________ Post ads for free - to sell, rent or even buy.www.yello.in http://ss1.richmedia.in/recurl.asp?pid=3D186= --_589b3967-e077-404f-b52e-09ca4a4218a7_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,

I've been working on a timezone issue. I= am in Adelaide Australia and the daylight savings time this year has been = changed. I am trying to figure out the file which is required to be changed= .

I've done the below research:

1) Checked the postgresql= .conf file and the timezone parameter is set as "unknown"= . According to the documentation, if this parameter is set to unknown then = postgresql will first look at the TZ parameter, if the TZ parameter doesn't= exist then it looks at the system time.

This means that postgres should look at the OS level time but am conf= used with varied results from different queries as explained below. =

2) I ran the below query in 2 different postgresql databases and fo= und that both show different results

Server 1:

echo se= lect timestamp with time zone \'epoch\' + 1206970200 \* INTERVAL \'1 second= \'\; | psql template1
         = ?column?
---------------------------
 2008-03-31 23:00:00+09:3= 0
(1 row)

Server2:

echo select timestamp with ti= me zone \'epoch\' + 1206970200 \* INTERVAL \'1 second\'\; | psql template1 =
         ?column?
---------= ------------------
 2008-04-01 00:00:00+10:30
(1 row)

=
Server 2 is right.


3) I ran the below command:
/local/pkg/share/postgresql/timezone/Australia $ /usr/sbin/zdump -v Aus= tralia/Adelaide | grep 2007

Australia/Adelaide  Sat Mar 24 = 16:29:59 2007 UTC =3D Sun Mar 25 02:59:59 2007 CST isdst=3D1
Australia/A= delaide  Sat Mar 24 16:30:00 2007 UTC =3D Sun Mar 25 02:00:00 2007 CST= isdst=3D0
Australia/Adelaide  Sat Oct 27 16:29:59 2007 UTC =3D Sun= Oct 28 01:59:59 2007 CST isdst=3D0
Australia/Adelaide  Sat Oct 27 = 16:30:00 2007 UTC =3D Sun Oct 28 03:00:00 2007 CST isdst=3D1

Is this above detail from OS level or from Postgre= s Level ?
<= BR>
4) I tried finding the timezone files and found them under /local= /pkg/share/postgresql/timezone/Australia

I tried opening the Ade= laide file under this folder but the file is not readable.

Can you please let me know if i need to change = the settings in this file or in someother file. I am not able to find good = documentation on this.

5) One more query:

/local/= pkgsrc/databases/postgresql80-pgcrypto/work/postgresql-8.0.9/src/timezone/d= ata $ grep 2007 australasia<= BR>Rule    AS      2007  =   max     -       Ma= r     lastSun 2:00s   0   &nbs= p;   -
Rule    AT     = 2007    max     -   &nbs= p;   Mar     lastSun 2:00s   0 = ;      -
Rule    AV  &= nbsp;   2007    max     - = ;      Mar     lastSun 2:00s&n= bsp;  0       -
Rule   = ; AN      2007    max  &n= bsp;  -       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 $ g= rep 2008 australasia

No results

Basically, i want = to know from where does postgresql get the time and where should i modify t= he DST settings.

Thanks and Regards,
Shilpa


Live the life in style with MSN Lifestyle. Check out! Try it now!



Post free auto ads on Yel= lo Classifieds now! Try it now! = --_589b3967-e077-404f-b52e-09ca4a4218a7_--