Thread: 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
Post free auto ads on Yello Classifieds now! 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 >
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,
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,
On Fri, Mar 7, 2008 at 4:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Vishal Arora <aroravishal22@hotmail.com> writes:The easiest and best solution would be to update to a newer PG release
> 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.
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