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
