pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Time 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