Thread: Re: Argentinian timezone change at the last moment. How to change pgsql tz db?

Wow!  I just looked this up on the web, and all I can say is, this was
a really stupid idea on the part of the govt in Argentina.     It
takes more than a couple days to create new timezone files and deploy
them normally.

I've been reading up on zic and wondering if it's a reasonable thing
to try and update the pg tz db to include the new argentinian DST
change.  Where is the tz info stored in postgres?  In the catalog?
I'd be willing to take a whack at making a new tz file for argentina
if I knew where it was and how to change it.

(scuttles off to look at the 8.2.5 source)

Scott Marlowe wrote:
> I've been reading up on zic and wondering if it's a reasonable thing
> to try and update the pg tz db to include the new argentinian DST
> change.  Where is the tz info stored in postgres?  In the catalog?
>   
Typically in /usr/share/postgresql/timezone or maybe 
/usr/local/share/postgresql/timezone.

Joe

On Jan 2, 2008 2:49 PM, Joe <dev@freedomcircle.net> wrote:
> Scott Marlowe wrote:
> > I've been reading up on zic and wondering if it's a reasonable thing
> > to try and update the pg tz db to include the new argentinian DST
> > change.  Where is the tz info stored in postgres?  In the catalog?
> >
> Typically in /usr/share/postgresql/timezone or maybe
> /usr/local/share/postgresql/timezone.

That doesn't get me what I need.  It lets me change the alias of
timezones, but not the start and stop of daylight savings time.  I
think for that I'd have to edit / replace the files in
postgresql-8.2.x/src/timezone/data/ and recompile to fix this.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> That doesn't get me what I need.  It lets me change the alias of
> timezones, but not the start and stop of daylight savings time.  I
> think for that I'd have to edit / replace the files in
> postgresql-8.2.x/src/timezone/data/ and recompile to fix this.

Since the OP has apparently already managed to get updated tzdata files
installed on his system, he could just copy them into
/usr/share/postgresql/timezone --- anything using zic should be a
compatible file format.

The lack-of-ARST-on-input problem can be addressed by mucking with
/usr/share/postgresql/timezonesets/Default, if you're using 8.2.
In earlier versions the table is hardwired into datetime.c :-(
        regards, tom lane

> Tom Lane wrote:
> 
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > That doesn't get me what I need.  It lets me change the alias of
> > timezones, but not the start and stop of daylight savings time.  I
> > think for that I'd have to edit / replace the files in
> > postgresql-8.2.x/src/timezone/data/ and recompile to fix this.
> 
> Since the OP has apparently already managed to get updated tzdata files
> installed on his system, he could just copy them into
> /usr/share/postgresql/timezone --- anything using zic should be a
> compatible file format.
> 
> The lack-of-ARST-on-input problem can be addressed by mucking with
> /usr/share/postgresql/timezonesets/Default, if you're using 8.2.
> In earlier versions the table is hardwired into datetime.c :-(
> 
>             regards, tom lane

Thanks Scott and Tom for your help on this.

After copying the updated tz file to /usr/share/postgresql/timezone Postgres
got aware of the time change.

Regarding the ARST recognition, I'm still on 8.1.9. :(
An upgrade seems urgent now.

Regards,
Fernando.

> Tom Lane [mailto:tgl@sss.pgh.pa.us] wrote:
>
> Since the OP has apparently already managed to get updated tzdata files
> installed on his system, he could just copy them into
> /usr/share/postgresql/timezone --- anything using zic should be a
> compatible file format.
>
> The lack-of-ARST-on-input problem can be addressed by mucking with
> /usr/share/postgresql/timezonesets/Default, if you're using 8.2.
> In earlier versions the table is hardwired into datetime.c :-(
>

Summing up:

After installing the updated tzdata files in the server I had to copy the
America/Argentina/* files to /usr/share/postgresql/timezone in order to get
postgres determine the correct local time.

With 8.2.x the ARST abbreviation was recognized after including the
following line in /usr/share/postgresql/8.2/timezonesets/Default

ARST   -14400 D  # Argentina Summer Time

postgres=# select '01:13:16.426 ARST Wed Jan 2 2008'::timestamp with time
zone;
        timestamptz
----------------------------
 2008-01-02 01:13:16.426-02
(1 row)

I wonder if pg_timezone_names plays any role in the ARST issue. It does
contain the right data (appeared after copying tzdata into
/usr/share/postgresql/timezone and restarting server) but ARST wasn't
accepted till previous step was done.

postgres=# select * from pg_timezone_names where abbrev = 'ARST';
              name              | abbrev | utc_offset | is_dst
--------------------------------+--------+------------+--------
 localtime                      | ARST   | -02:00:00  | t
 America/Argentina/Rio_Gallegos | ARST   | -02:00:00  | t
 America/Argentina/Mendoza      | ARST   | -02:00:00  | t
 America/Argentina/La_Rioja     | ARST   | -02:00:00  | t
 America/Argentina/Buenos_Aires | ARST   | -02:00:00  | t
 America/Argentina/Cordoba      | ARST   | -02:00:00  | t
 America/Argentina/Catamarca    | ARST   | -02:00:00  | t
 America/Argentina/Ushuaia      | ARST   | -02:00:00  | t
 America/Argentina/Tucuman      | ARST   | -02:00:00  | t
 America/Argentina/Jujuy        | ARST   | -02:00:00  | t
 America/Argentina/San_Juan     | ARST   | -02:00:00  | t
(11 rows)


Thanks for all contributions.

Regards,
Fernando.

"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> With 8.2.x the ARST abbreviation was recognized after including the
> following line in /usr/share/postgresql/8.2/timezonesets/Default

> ARST   -14400 D  # Argentina Summer Time

Um ... is that really offsetting in the correct direction?  What I put
into CVS was

ARST    -7200 D  # Argentina Summer Time

If that's wrong I need to know ...

> I wonder if pg_timezone_names plays any role in the ARST issue. It does
> contain the right data (appeared after copying tzdata into
> /usr/share/postgresql/timezone and restarting server) but ARST wasn't
> accepted till previous step was done.

Too tired to experiment, but you may need a "pg_ctl reload" or even a
restart to get PG to notice changes in your timezonesets file.

            regards, tom lane

> Tom Lane [mailto:tgl@sss.pgh.pa.us] wrote:
>
> "Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> > With 8.2.x the ARST abbreviation was recognized after including the
> > following line in /usr/share/postgresql/8.2/timezonesets/Default
>
> > ARST   -14400 D  # Argentina Summer Time
>
> Um ... is that really offsetting in the correct direction?  What I put
> into CVS was
>
> ARST    -7200 D  # Argentina Summer Time
>
> If that's wrong I need to know ...
>

No, you are right: -7200 is the correct offset.

Regards,
Fernando.