Thread: diferent timezones in the same table?

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 =3D 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=3D# show timezone;
 TimeZone
----------
 GMT+5
(1 row)

imrelevsa=3D# select '1900-01-01 00:00:00'::timestamp with time zone;
      timestamptz
------------------------
 1900-01-01 00:00:00-05
(1 row)

imrelevsa=3D# set timezone to 'America/Guayaquil';
SET
imrelevsa=3D# 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?

--=20
Atentamente,
Jaime Casanova
Soporte y capacitaci=C3=B3n de PostgreSQL
Asesor=C3=ADa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> 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)...

This will be interpreted as 1900-01-01 00:00:00 in whatever timezone
is selected when you insert the value...

> timezone 'America/Guayaquil'

... and in that zone, times before 1931 are taken to be local mean
solar time, which is 5 hours and change off GMT.  See the Olsen data
files:

# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone America/Guayaquil    -5:19:20 -    LMT    1890
            -5:14:00 -    QMT    1931 # Quito Mean Time
            -5:00    -    ECT         # Ecuador Time

            regards, tom lane