pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTime Zone in Postgres
18+ messages / 6 participants
[nested] [flat]
* Time Zone in Postgres
@ 2008-03-06 06:45 Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Vishal Arora @ 2008-03-06 06:45 UTC (permalink / raw)
To: Shilpa <shilpa.sudhakar@gmail.com>; 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 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 CST isdst=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=
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
@ 2008-03-06 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:53 ` Re: Time Zone in Postgres Shilpa <shilpa.sudhakar@gmail.com>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 2 replies; 18+ messages in thread
From: Tom Lane @ 2008-03-06 18:23 UTC (permalink / raw)
To: Vishal Arora <aroravishal22@hotmail.com>; +Cc: Shilpa <shilpa.sudhakar@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-06 22:53 ` Shilpa <shilpa.sudhakar@gmail.com>
1 sibling, 0 replies; 18+ messages in thread
From: Shilpa @ 2008-03-06 22:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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,
On Fri, Mar 7, 2008 at 4:53 AM, Tom Lane <tgl@sss.pgh.pa.us> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-06 22:56 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
1 sibling, 1 reply; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-06 22:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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_wwwusr?domain=postgresql.org&extra=pgsql-admin
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-03-06 23:46 ` cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 1 reply; 18+ messages in thread
From: cat@zip.com.au @ 2008-03-06 23:46 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
@ 2008-03-07 00:18 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:54 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-07 00:18 UTC (permalink / raw)
To: cat@zip.com.au; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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.
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-03-07 00:27 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:43 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
1 sibling, 2 replies; 18+ messages in thread
From: Alvaro Herrera @ 2008-03-07 00:27 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: cat@zip.com.au, Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-03-07 00:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
1 sibling, 1 reply; 18+ messages in thread
From: Tom Lane @ 2008-03-07 00:39 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Shilpa Sudhakar <ssudhakar@internode.com.au>; cat@zip.com.au; 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/national/daylight-savings-to-span-six-months/2007/06/27/1182623966703....
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-07 00:45 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 01:00 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:05 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-07 00:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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/national/daylight-savings-to-span-six-months/2007/06/27/1182623966703....
>
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-03-07 01:00 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 18+ messages in thread
From: Alvaro Herrera @ 2008-03-07 01:00 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-03-07 01:05 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:21 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 18+ messages in thread
From: Alvaro Herrera @ 2008-03-07 01:05 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 01:05 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-03-07 01:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 01:51 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2008-03-07 01:21 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Shilpa Sudhakar <ssudhakar@internode.com.au>; pgsql-admin
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 01:05 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:21 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-07 01:51 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 02:49 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-07 01:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 01:05 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:21 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 01:51 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-03-07 02:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 05:32 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2008-03-07 02:49 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 01:05 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:21 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 01:51 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 02:49 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-07 05:32 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 0 replies; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-07 05:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Re: Time Zone in Postgres Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-03-07 00:43 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
1 sibling, 0 replies; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-07 00:43 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: 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.
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
@ 2008-03-07 00:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:59 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
1 sibling, 1 reply; 18+ messages in thread
From: Tom Lane @ 2008-03-07 00:54 UTC (permalink / raw)
To: Shilpa Sudhakar <ssudhakar@internode.com.au>; +Cc: cat@zip.com.au; 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Time Zone in Postgres
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:56 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` Re: Time Zone in Postgres cat@zip.com.au
2008-03-07 00:18 ` Re: Time Zone in Postgres Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:54 ` Re: Time Zone in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-07 00:59 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
0 siblings, 0 replies; 18+ messages in thread
From: Shilpa Sudhakar @ 2008-03-07 00:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2008-03-07 05:32 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-06 06:45 Time Zone in Postgres Vishal Arora <aroravishal22@hotmail.com>
2008-03-06 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-06 22:53 ` Shilpa <shilpa.sudhakar@gmail.com>
2008-03-06 22:56 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-06 23:46 ` cat@zip.com.au
2008-03-07 00:18 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:27 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 00:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:45 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 01:00 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:05 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-07 01:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 01:51 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 02:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 05:32 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:43 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
2008-03-07 00:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 00:59 ` Shilpa Sudhakar <ssudhakar@internode.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox