pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Argentinian timezone change at the last moment. How to change pgsql tz db?
8+ messages / 4 participants
[nested] [flat]
* Re: Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-02 20:18 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2008-01-02 20:18 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-02 20:49 Joe <dev@freedomcircle.net>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Joe @ 2008-01-02 20:49 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Fernando Hevia <fhevia@ip-tel.com.ar>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-02 23:29 Scott Marlowe <scott.marlowe@gmail.com>
parent: Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2008-01-02 23:29 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: Fernando Hevia <fhevia@ip-tel.com.ar>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-02 23:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2008-01-02 23:45 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Joe <dev@freedomcircle.net>; Fernando Hevia <fhevia@ip-tel.com.ar>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-03 12:27 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Fernando Hevia @ 2008-01-03 12:27 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'Scott Marlowe' <scott.marlowe@gmail.com>; +Cc: 'Joe' <dev@freedomcircle.net>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-04 03:57 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Fernando Hevia @ 2008-01-04 03:57 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'Scott Marlowe' <scott.marlowe@gmail.com>; +Cc: 'Joe' <dev@freedomcircle.net>; pgsql-sql; pgsql-general@postgresql.org
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-04 04:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2008-01-04 04:37 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: 'Scott Marlowe' <scott.marlowe@gmail.com>; 'Joe' <dev@freedomcircle.net>; pgsql-sql; pgsql-general@postgresql.org
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
@ 2008-01-04 14:01 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Fernando Hevia @ 2008-01-04 14:01 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'Scott Marlowe' <scott.marlowe@gmail.com>; 'Joe' <dev@freedomcircle.net>; pgsql-sql; pgsql-general@postgresql.org
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-01-04 14:01 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-02 20:18 Re: Argentinian timezone change at the last moment. How to change pgsql tz db? Scott Marlowe <scott.marlowe@gmail.com>
2008-01-02 20:49 ` Joe <dev@freedomcircle.net>
2008-01-02 23:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-02 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-03 12:27 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2008-01-04 03:57 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2008-01-04 04:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-04 14:01 ` Fernando Hevia <fhevia@ip-tel.com.ar>
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