Обсуждение: Time Zone in Postgres

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!

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

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_wwwusr?domain=postgresql.org&extra=pgsql-admin
>

Hi Cat,

As per my previous mail, i've done few checks on the postgres timezone
files but not sure if am looking at the right file.

I'll paste the details again below .

1) Checked the *postgresql.conf file* and the *timezone *parameter is
set as "*unknown*".

2)
*/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

 From 2008, the DST ends on first Sunday of April and not on the last
Sun of March.

Thanks



cat@zip.com.au wrote:
> 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.
>
>

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

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.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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/national/daylight-savings-to-span-six-months/2007/06/27/1182623966703.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

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.
>
>

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/national/daylight-savings-to-span-six-months/2007/06/27/1182623966703.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
>

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

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
>

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.

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.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Shilpa Sudhakar wrote:
>> 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.

... and 8.0.14 ...

Anyway, yes, you could get the newer zoneinfo source files and run them
through zic, but it's hard to see why that would be a smarter thing to
do than a database update.  There are known data-loss bugs in the minor
version you're running, and you ought to be a lot more worried about
those than about whether Postgres is a week off about the next DST
transition.

            regards, tom lane

Hi Tom,

Thanks. I'll look into upgrading the database. Since we have a huge
bunch of databases i was thinking of an alternative for now.

As i said, we have version 8.2.4 databases and if i need to just update
the timezone files for this database do i copy the zoneinfo files from
the OS zonefile or from the newer version of Postgres zonefiles.

Thanks


Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>
>> Shilpa Sudhakar wrote:
>>
>>> 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.
>>
>
> ... and 8.0.14 ...
>
> Anyway, yes, you could get the newer zoneinfo source files and run them
> through zic, but it's hard to see why that would be a smarter thing to
> do than a database update.  There are known data-loss bugs in the minor
> version you're running, and you ought to be a lot more worried about
> those than about whether Postgres is a week off about the next DST
> transition.
>
>             regards, tom lane
>

Shilpa Sudhakar <ssudhakar@internode.com.au> writes:
> As i said, we have version 8.2.4 databases and if i need to just update
> the timezone files for this database do i copy the zoneinfo files from
> the OS zonefile or from the newer version of Postgres zonefiles.

If your platform uses the standard zoneinfo format (and if the file
trees look similar, it probably does) then you could just copy the
system's zoneinfo files into the Postgres directories, assuming you've
gotten updated zoneinfo files for the system.

            regards, tom lane

I copied the system zonefiles into the postgres database and it works
fine now. This is anyway a temporary solution until we upgrade all our
systems.

Thanks to all for your suggestions and help.



Tom Lane wrote:
> Shilpa Sudhakar <ssudhakar@internode.com.au> writes:
>
>> As i said, we have version 8.2.4 databases and if i need to just update
>> the timezone files for this database do i copy the zoneinfo files from
>> the OS zonefile or from the newer version of Postgres zonefiles.
>>
>
> If your platform uses the standard zoneinfo format (and if the file
> trees look similar, it probably does) then you could just copy the
> system's zoneinfo files into the Postgres directories, assuming you've
> gotten updated zoneinfo files for the system.
>
>             regards, tom lane
>