Thread: 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
On Jan 2, 2008 12:43 PM, Fernando Hevia <fhevia@ip-tel.com.ar> 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 | fAmerica/Argentina/Buenos_Aires | ART | -03:00:00 | fAmerica/Argentina/San_Juan | ART | -03:00:00 | fAmerica/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 | tAmerica/Montevideo | UYST | -02:00:00 | tBrazil/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.
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: # <a href="http://www.lanacion.com.ar/politica/nota.asp?nota_id=973996"> # http://www.lanacion.com.ar/politica/nota.asp?nota_id=973996 # </a> # # 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. Rule Arg 2007 only - Dec 30 0:00 1:00 S Rule Arg 2008 max - Mar Sun>=15 0:00 0 - Rule Arg 2008 max - Oct Sun>=1 0:00 1:00 S 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.