From: | Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | diferent timezones in the same table? |
Date: | 2009-09-25 14:19:41 |
Message-ID: | 3073cc9b0909250719m1ab02b52i10b91f163d566eb7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
One of the tables in a client production system has several timestamp
with time zone fields, in one of those fields they store '1900-01-01
00:00:00'::timestamp with time zone where there is no valid value (a
legacy from another dbms)...
pg = 8.4.1
timezone 'America/Guayaquil'
yesterday we found that that field is getting obtained as year 1899
when investigating i find that is beign obtained as timezone GMT+5:14
as shown in this extract of data (look at the data in the other field,
is just fine but shows different info about time zone):
"""
fecha_registro | fecha_registro_retencion
------------------------+---------------------------
2009-07-09 00:00:00-05 | 1899-12-31 23:46:00-05:14
2009-07-07 00:00:00-05 | 1899-12-31 23:46:00-05:14
2009-07-27 00:00:00-05 | 1899-12-31 23:46:00-05:14
"""
i have the data in my test env and i found that this happen when
timezone is set to 'America/Guayaquil' but not if set it to 'GMT+5'
and only with values in '1900-01-01 00:00:00', even more in the same
field all values different from that date are right:
"""
imrelevsa=# show timezone;
TimeZone
----------
GMT+5
(1 row)
imrelevsa=# select '1900-01-01 00:00:00'::timestamp with time zone;
timestamptz
------------------------
1900-01-01 00:00:00-05
(1 row)
imrelevsa=# set timezone to 'America/Guayaquil';
SET
imrelevsa=# select '1900-01-01 00:00:00'::timestamp with time zone;
timestamptz
---------------------------
1900-01-01 00:00:00-05:14
(1 row)
"""
is this intended? why we treat '1900-01-01 00:00:00' different?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-09-25 14:29:24 | Re: diferent timezones in the same table? |
Previous Message | Stefan | 2009-09-25 08:14:55 | BUG #5081: ON INSERT rule does not work correctly |