[SQL] PG is in different timezone than the OS
Hi all, I am not sure if this is the correct list to post this issue. Please let me know if there is a more suitable one. Argentina's government has recently decreted a timezone change for the summer (daylight's savings) where local time zone changes from GMT-3 to GMT-2. The Argentinean Summer Timezone is named "ARST". My first problem is that Postgres still hangs with GMT-3 while OS is at GMT-2 *OS date* # date -R ; date Wed, 02 Jan 2008 16:07:36 -0200 Wed Jan 2 16:07:36 ARST 2008 *Postgres* radius=# select now()::timestamp with time zone; now --- 2008-01-02 15:07:59.435233-03 (1 row) As you can see PG is at GMT-03. Restart has been done to no effect. Postgres.conf settings are: # - Locale and Formatting - #datestyle = 'iso, mdy' #timezone = unknown # actually, defaults to TZ # environment setting #australian_timezones = off #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii# actually, defaults to database # encoding I have also tried with: timezone='America/Argentina/Cordoba' How do I tell postgres that it is located in Argentina/Cordoba or GMT-02? Is there a way to have it relay to the OS? My second problem is that Postgres doesn't recognize the timezone ARST. pg=# select '01:13:16.426 ARST Wed Jan 2 2008'::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: "01:13:16.426 ARST Wed Jan 2 2008" Whereas with the previous ART timezone it did well: pg=# select '01:13:16.426 ART Wed Jan 2 2008'::timestamp with time zone; timestamptz 2008-01-02 01:13:16.426-03 (1 row) I'm lost here. ARST isn't new. It has been used in former years. Any help would be greatly appreciated. Regards, Fernando ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PG is in different timezone than the OS
On Jan 2, 2008 12:43 PM, Fernando Hevia <[EMAIL PROTECTED]> wrote: > Hi all, > > I am not sure if this is the correct list to post this issue. Please let me > know if there is a more suitable one. > > Argentina's government has recently decreted a timezone change for the > summer (daylight's savings) where local time zone changes from GMT-3 to > GMT-2. The Argentinean Summer Timezone is named "ARST". Well, you're going to have an issue until the timezone databases get updated, then postgresql gets updated. The problem is that with date math you need to know when things change from one offset to another. I.e. ARST is not going to be a constant offset. It will change based on what date it is, right? If so, then simply setting the offset to -03:00:00 isn't going to fix your problem. Assuming you're on 8.2.x, you can look at the timezones available with these queries: select * from pg_timezone_abbrevs; select * from pg_timezone_names; If one looks in pg_timezone_names one can find these entries: America/Argentina/La_Rioja | ART| -03:00:00 | f America/Argentina/Buenos_Aires | ART| -03:00:00 | f America/Argentina/San_Juan | ART| -03:00:00 | f America/Argentina/Mendoza | ART| -03:00:00 | f and so on. There is no ARST in the database for 8.2.5 that I know of. select * from pg_timezone_names where utc_offset = '-02:00:00'::interval and is_dst is true; name | abbrev | utc_offset | is_dst --+++ America/Sao_Paulo| BRST | -02:00:00 | t America/Montevideo | UYST | -02:00:00 | t Brazil/East | BRST | -02:00:00 | t ... CUT for brevity shows a few timezones that are -0200 and have dst, which means that they'll go to -0300 in the spring. If the dates they change are the same as yours, you could use one of them. Try setting your timezone to one of those and see if the offset changes on the right date. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PG is in different timezone than the OS
Carrying on a convo with myself here. Looking in the timezone directory, and looking at the tz database located at ftp://elsie.nci.nih.gov/pub/ it appears the southamerica timezone data was updated 2007-12-13 at 9am or so. Looking through the file it looks like the change was made: # From Steffen Thorsen (2007-12-21): # A user (Leonardo Chaim) reported that Argentina will adopt DST # all of the country (all Zone-entries) are affected. News reports like # http://www.lanacion.com.ar/opinion/nota.asp?nota_id=973037 indicate # that Argentina will use DST next year as well, from October to # March, although exact rules are not given. # # From Jesper Norgaard Welen (2007-12-26) # The last hurdle of Argentina DST is over, the proposal was approved in # the lower chamber too (Deputados) with a vote 192 for and 2 against. # By the way thanks to Mariano Absatz and Daniel Mario Vega for the link to # the original scanned proposal, where the dates and the zero hours are # clear and unambiguous...This is the article about final approval: # http://www.lanacion.com.ar/politica/nota.asp?nota_id=973996";> # http://www.lanacion.com.ar/politica/nota.asp?nota_id=973996 # # # From Paul Eggert (2007-12-22): # For dates after mid-2008, the following rules are my guesses and # are quite possibly wrong, but are more likely than no DST at all. RuleArg 2007only- Dec 30 0:001:00S RuleArg 2008max - Mar Sun>=15 0:000 - RuleArg 2008max - Oct Sun>=1 0:001:00S So, it appears that the timezone folks know... Note that I added pgsql-general, as this isn't really a -sql question. Next person to reply please remove the -sql group. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] 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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
On Jan 2, 2008 2:49 PM, Joe <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?
"Scott Marlowe" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
