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 resultsServer 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 2007Australia/Adelaide Sat Mar 24 16:29:59 2007 UTC = Sun Mar 25 02:59:59 2007 CSTisdst=1Australia/Adelaide Sat Mar 24 16:30:00 2007 UTC = Sun Mar 25 02:00:00 2007 CST isdst=0Australia/Adelaide Sat Oct 27 16:29:59 2007 UTC = Sun Oct 28 01:59:59 2007 CST isdst=0Australia/Adelaide Sat Oct 27 16:30:00 2007 UTC = Sun Oct 28 03:00:00 2007 CST isdst=1Is 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/AustraliaI 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 australasiaRule 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 australasiaNo resultsBasically, 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!
__________________________________________________ _______________
Post ads for free - to sell, rent or even buy.www.yello.in
http://ss1.richmedia.in/recurl.asp?pid=186

Vishal Arora <aroravishal22@hotmail.com> writes:
> 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.

The easiest and best solution would be to update to a newer PG release
than 8.0.9 --- installing 8.0.15 over it would be painless and would fix
a number of problems besides this one, including some rather nasty
data-loss risks.

If you really can't be bothered with that, you can probably use your
operating system's copy of the timezone data files (try under
/usr/share/zoneinfo for starters).

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ra=pgsql-admin

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 *

Thanks and Regards,

Tom Lane wrote:
> Vishal Arora <aroravishal22@hotmail.com> writes:
>
>> 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.
>>
>
> The easiest and best solution would be to update to a newer PG release
> than 8.0.9 --- installing 8.0.15 over it would be painless and would fix
> a number of problems besides this one, including some rather nasty
> data-loss risks.
>
> If you really can't be bothered with that, you can probably use your
> operating system's copy of the timezone data files (try under
> /usr/share/zoneinfo for starters).
>
> regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_www...ra=pgsql-admin
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ra=pgsql-admin

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Shilpa Sudhakar wrote:
>> From 2008, the DST ends on first Sunday of April and not on the last Sun
>> of March.

> Please report this issue to the guys maintaining the TZ database
> upstream:

You sure they don't know about it already?

# southeast Australia
#
# From Paul Eggert (2007-07-23):
# Starting autumn 2008 Victoria, NSW, South Australia, Tasmania and the ACT
# end DST the first Sunday in April and start DST the first Sunday in October.
# http://www.theage.com.au/news/nation...623966703.html

This is in our releases 8.2.5 and up. If the OP wants to live under a
government that feels free to whack DST laws around on a couple months'
notice, he's got to be prepared to keep up with software updates.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Hi Alvaro,

Thanks for the info.

I assume Postgresql should have already known about the DST changes in
Australia.

I'll check the mailing list from the link you sent to see if there's
anything regarding this.

Thanks and Regards,



Alvaro Herrera wrote:
> Shilpa Sudhakar wrote:
>
>
>> From 2008, the DST ends on first Sunday of April and not on the last Sun
>> of March.
>>
>
> Please report this issue to the guys maintaining the TZ database
> upstream:
>
> http://news.gmane.org/gmane.comp.time.tz
>
> Because my country is having a TZ DST change this Saturday, a new
> release of tzdata is coming out tomorrow or the day after, so if this
> issue wasn't already reported you'll probably miss it. Still, it would
> be very good to be quick about it so your changes are present in the
> next release due shortly thereafter.
>
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Shilpa Sudhakar <ssudhakar@internode.com.au> writes:
> 1) Checked the *postgresql.conf file* and the *timezone *parameter is
> set as "*unknown*".

What does SHOW TIMEZONE report? Given the above, Postgres will try to
deduce what zone your operating system is using, but that doesn't always
work perfectly (especially if PG's timezone database isn't quite in sync
with the system's). If it guessed wrong, you might have to explicitly
set the appropriate zone in postgresql.conf. You weren't very clear
about where you live, so I don't know which of the numerous
Australia/wherever zones you should use...

> */local/pkgsrc/databases/postgresql80-pgcrypto/work/postgresql-8.0.9/src/timezone/data
> $ grep 2008 australasia*

> No results

Well, no. 8.0.9 was released on 2006-10-16, many months before that
change in DST laws was passed. You need a newer set of zoneinfo files.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Hi Tom,

If DST changes are done in version 8.2.5 and up, is there any way to
recompile the timezone files in our existing versions without upgrading.

Thanks

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>
>> Shilpa Sudhakar wrote:
>>
>>> From 2008, the DST ends on first Sunday of April and not on the last Sun
>>> of March.
>>>
>
>
>> Please report this issue to the guys maintaining the TZ database
>> upstream:
>>
>
> You sure they don't know about it already?
>
> # southeast Australia
> #
> # From Paul Eggert (2007-07-23):
> # Starting autumn 2008 Victoria, NSW, South Australia, Tasmania and the ACT
> # end DST the first Sunday in April and start DST the first Sunday in October.
> # http://www.theage.com.au/news/nation...623966703.html
>
> This is in our releases 8.2.5 and up. If the OP wants to live under a
> government that feels free to whack DST laws around on a couple months'
> notice, he's got to be prepared to keep up with software updates.
>
> regards, tom lane
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Shilpa Sudhakar wrote:
> Hi Tom,
>
> If DST changes are done in version 8.2.5 and up, is there any way to
> recompile the timezone files in our existing versions without upgrading.

Why would you do that? It's silly. Just upgrade.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Hi Tom,

I live in Adelaide Australia. I checked in 3 servers and all of them
have "Australia/South" when i ran "show timezone"

For the new timezone files, do i need to apply a timezone patch?

Thanks


Tom Lane wrote:
> Shilpa Sudhakar <ssudhakar@internode.com.au> writes:
>
>> 1) Checked the *postgresql.conf file* and the *timezone *parameter is
>> set as "*unknown*".
>>
>
> What does SHOW TIMEZONE report? Given the above, Postgres will try to
> deduce what zone your operating system is using, but that doesn't always
> work perfectly (especially if PG's timezone database isn't quite in sync
> with the system's). If it guessed wrong, you might have to explicitly
> set the appropriate zone in postgresql.conf. You weren't very clear
> about where you live, so I don't know which of the numerous
> Australia/wherever zones you should use...
>
>
>> */local/pkgsrc/databases/postgresql80-pgcrypto/work/postgresql-8.0.9/src/timezone/data
>> $ grep 2008 australasia*
>>
>
>
>> No results
>>
>
> Well, no. 8.0.9 was released on 2006-10-16, many months before that
> change in DST laws was passed. You need a newer set of zoneinfo files.
>
> regards, tom lane
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Shilpa Sudhakar wrote:
> Hi Tom,
>
> If DST changes are done in version 8.2.5 and up, is there any way to
> recompile the timezone files in our existing versions without upgrading.

FWIW this change is also in 8.1.11.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin