Time Zone in Postgres - Mailing list pgsql-admin

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 confused with varied results from different queries as explained below.

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

Server 1:

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

Server2:

echo select timestamp with time 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 Australia/Adelaide | grep 2007

Australia/Adelaide  Sat Mar 24 16:29:59 2007 UTC = Sun Mar 25 02:59:59 2007 CST isdst=1
Australia/Adelaide  Sat Mar 24 16:30:00 2007 UTC = Sun Mar 25 02:00:00 2007 CST isdst=0
Australia/Adelaide  Sat Oct 27 16:29:59 2007 UTC = Sun Oct 28 01:59:59 2007 CST isdst=0
Australia/Adelaide  Sat Oct 27 16:30:00 2007 UTC = Sun Oct 28 03:00:00 2007 CST isdst=1

Is this above detail from OS level or from Postgres Level ?


4) I tried finding the timezone files and found them under /local/pkg/share/postgresql/timezone/Australia

I tried opening the Adelaide 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/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

Basically, i want to know from where does postgresql get the time 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!