Thread: Odd behaviour of timestamptz

Odd behaviour of timestamptz

From
Matteo Beccati
Date:
Hi,

I've been recently pointed out an issue with timestamptz on a fedora box
and no one was able to replicate it on other machines. After a quick
chat on the IRC at least another two people could replicate the issue
and all of them were using an RPM package.


PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-3)

test=# create TABLE test (data timestamp with time zone);
CREATE TABLE

test=# INSERT into test values ('1910-01-10');
INSERT 0 1

test=# INSERT into test values ('1990-01-10');
INSERT 0 1

test=# SELECT * from test;
           data
----------------------------
1910-01-10 00:00:00+00:19:32
1990-01-10 00:00:00+01
(2 rows)


Similar issues were reported using 8.1.8 on RHEL3:

test=# SELECT * from test2;
           data
---------------------------
 1910-01-10 00:00:00+00:09
 1990-01-10 00:00:00+01
(2 rows)


another 8.2.3 on FC3:

test=# SELECT * from test;
             data
------------------------------
 1910-01-10 00:00:00+00:09:21
 1990-01-10 00:00:00+01
(2 lignes)


and another machine:

test=# SELECT * from test;
           data
---------------------------
 1910-01-10 00:00:00+01:24
 1990-01-10 00:00:00+01
(2 rows)


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

Re: Odd behaviour of timestamptz

From
Michael Glaesemann
Date:
On Feb 21, 2007, at 17:15 , Matteo Beccati wrote:

> PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 3.4.6 20060404 (Red Hat 3.4.6-3)
>
> test=# create TABLE test (data timestamp with time zone);
> CREATE TABLE
>
> test=# INSERT into test values ('1910-01-10');
> INSERT 0 1
>
> test=# INSERT into test values ('1990-01-10');
> INSERT 0 1
>
> test=# SELECT * from test;
>            data
> ----------------------------
> 1910-01-10 00:00:00+00:19:32
> 1990-01-10 00:00:00+01
> (2 rows)

I suspect your RPMs build PostgreSQL without --enable-integer-
datetimes. Without this configure flag, timestamps are represented as
floats, with all of the imprecision that implies. See the second note
below the Date/Time Types table:

http://www.postgresql.org/docs/8.2/interactive/datatype-
datetime.html#DATATYPE-DATETIME-TABLE

Michael Glaesemann
grzm seespotcode net



Re: Odd behaviour of timestamptz

From
Matteo Beccati
Date:
Matteo Beccati ha scritto:
> Hi,
>
> I've been recently pointed out an issue with timestamptz on a fedora box
> and no one was able to replicate it on other machines. After a quick
> chat on the IRC at least another two people could replicate the issue
> and all of them were using an RPM package.
>
> [...]
> and another machine:
>
> test=# SELECT * from test;
>            data
> ---------------------------
>  1910-01-10 00:00:00+01:24
>  1990-01-10 00:00:00+01
> (2 rows)

Actually this one was built from source (CVS HEAD from yesterday), so
it's not a packaging issue as I first was thinking.


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

Re: Odd behaviour of timestamptz

From
Matteo Beccati
Date:
Hi,

Michael Glaesemann wrote:
>> test=# SELECT * from test;
>>            data
>> ----------------------------
>> 1910-01-10 00:00:00+00:19:32
>> 1990-01-10 00:00:00+01
>> (2 rows)
>
> I suspect your RPMs build PostgreSQL without --enable-integer-datetimes.
> Without this configure flag, timestamps are represented as floats, with
> all of the imprecision that implies. See the second note below the
> Date/Time Types table:
>
> http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-TABLE

In fact I was thinking to the opposite, but at least one of them didn't
use --enable-integer-datetimes. I could understand problems representing
microseconds, but not the time zone...


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

Re: Odd behaviour of timestamptz

From
"hubert depesz lubaczewski"
Date:
On 2/21/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
I suspect your RPMs build PostgreSQL without --enable-integer-
datetimes. Without this configure flag, timestamps are represented as
floats, with all of the imprecision that implies. See the second note
below the Date/Time Types table:

doesn't seem to be relevant.
i got brand new cvs head (about 40 minutes ago). configured it with:
./configure \
        --prefix=/home/pgdba/work \
        --with-pgport=5810 \
        --with-tcl \
        --with-perl \
        --with-python \
        --enable-integer-datetimes \
        --without-krb5 \
        --without-pam \
        --without-bonjour \
        --with-openssl \
        --with-readline \
        --with-zlib \
        --with-gnu-ld

and rerun the test:
# create TABLE test (data timestamptz);
CREATE TABLE

# INSERT into test values ('1910-01-10');
INSERT 0 1

# INSERT into test values ('1990-01-10');
INSERT 0 1

# select * from test;
           data
---------------------------
 1910-01-10 00:00:00+01:24
 1990-01-10 00:00:00+01
(2 rows)


still something's wrong.

depesz

Re: Odd behaviour of timestamptz

From
Martijn van Oosterhout
Date:
On Wed, Feb 21, 2007 at 09:40:05AM +0100, Matteo Beccati wrote:
> > test=# SELECT * from test;
> >            data
> > ---------------------------
> >  1910-01-10 00:00:00+01:24
> >  1990-01-10 00:00:00+01
> > (2 rows)
>
> Actually this one was built from source (CVS HEAD from yesterday), so
> it's not a packaging issue as I first was thinking.

Hmm, 1910 is before the time that timezones where spaced in nice
intervals of an hour, and there where plenty of places in the world
that had very odd offsets relative to what is used today.

Between 1909 and 1937 Amsterdam was 19 minutes 32.13 seconds ahead of
GMT, so I imagine entiring a time in that period will produce similar
results.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: Odd behaviour of timestamptz

From
Tom Lane
Date:
Matteo Beccati <php@beccati.com> writes:
> test=# INSERT into test values ('1910-01-10');
> INSERT 0 1

> test=# INSERT into test values ('1990-01-10');
> INSERT 0 1

> test=# SELECT * from test;
>            data
> ----------------------------
> 1910-01-10 00:00:00+00:19:32
> 1990-01-10 00:00:00+01
> (2 rows)

I'll bet you are running in Europe/Amsterdam time zone?  The above is
correct behavior according to the zic data files (see below).

            regards, tom lane


# Netherlands

# Howse writes that the Netherlands' railways used GMT between 1892 and 1940,
# but for other purposes the Netherlands used Amsterdam mean time.

# However, Robert H. van Gent writes (2001-04-01):
# Howse's statement is only correct up to 1909. From 1909-05-01 (00:00:00
# Amsterdam mean time) onwards, the whole of the Netherlands (including
# the Dutch railways) was required by law to observe Amsterdam mean time
# (19 minutes 32.13 seconds ahead of GMT). This had already been the
# common practice (except for the railways) for many decades but it was
# not until 1909 when the Dutch government finally defined this by law.
# On 1937-07-01 this was changed to 20 minutes (exactly) ahead of GMT and
# was generally known as Dutch Time ("Nederlandse Tijd").
#
# (2001-04-08):
# 1892-05-01 was the date when the Dutch railways were by law required to
# observe GMT while the remainder of the Netherlands adhered to the common
# practice of following Amsterdam mean time.
#
# (2001-04-09):
# In 1835 the authorities of the province of North Holland requested the
# municipal authorities of the towns and cities in the province to observe
# Amsterdam mean time but I do not know in how many cases this request was
# actually followed.
#
# From 1852 onwards the Dutch telegraph offices were by law required to
# observe Amsterdam mean time. As the time signals from the observatory of
# Leiden were also distributed by the telegraph system, I assume that most
# places linked up with the telegraph (and railway) system automatically
# adopted Amsterdam mean time.
#
# Although the early Dutch railway companies initially observed a variety
# of times, most of them had adopted Amsterdam mean time by 1858 but it
# was not until 1866 when they were all required by law to observe
# Amsterdam mean time.

# The data before 1945 are taken from
# <http://www.phys.uu.nl/~vgent/wettijd/wettijd.htm>.

# Rule    NAME    FROM    TO    TYPE    IN    ON    AT    SAVE    LETTER/S
Rule    Neth    1916    only    -    May     1    0:00    1:00    NST    # Netherlands Summer Time
Rule    Neth    1916    only    -    Oct     1    0:00    0    AMT    # Amsterdam Mean Time
Rule    Neth    1917    only    -    Apr    16    2:00s    1:00    NST
Rule    Neth    1917    only    -    Sep    17    2:00s    0    AMT
Rule    Neth    1918    1921    -    Apr    Mon>=1    2:00s    1:00    NST
Rule    Neth    1918    1921    -    Sep    lastMon    2:00s    0    AMT
Rule    Neth    1922    only    -    Mar    lastSun    2:00s    1:00    NST
Rule    Neth    1922    1936    -    Oct    Sun>=2    2:00s    0    AMT
Rule    Neth    1923    only    -    Jun    Fri>=1    2:00s    1:00    NST
Rule    Neth    1924    only    -    Mar    lastSun    2:00s    1:00    NST
Rule    Neth    1925    only    -    Jun    Fri>=1    2:00s    1:00    NST
# From 1926 through 1939 DST began 05-15, except that it was delayed by a week
# in years when 05-15 fell in the Pentecost weekend.
Rule    Neth    1926    1931    -    May    15    2:00s    1:00    NST
Rule    Neth    1932    only    -    May    22    2:00s    1:00    NST
Rule    Neth    1933    1936    -    May    15    2:00s    1:00    NST
Rule    Neth    1937    only    -    May    22    2:00s    1:00    NST
Rule    Neth    1937    only    -    Jul     1    0:00    1:00    S
Rule    Neth    1937    1939    -    Oct    Sun>=2    2:00s    0    -
Rule    Neth    1938    1939    -    May    15    2:00s    1:00    S
Rule    Neth    1945    only    -    Apr     2    2:00s    1:00    S
Rule    Neth    1945    only    -    Sep    16    2:00s    0    -
#
# Amsterdam Mean Time was +00:19:32.13 exactly, but the .13 is omitted
# below because the current format requires GMTOFF to be an integer.
# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone Europe/Amsterdam    0:19:32 -    LMT    1835
            0:19:32    Neth    %s    1937 Jul  1
            0:20    Neth    NE%sT    1940 May 16 0:00 # Dutch Time
            1:00    C-Eur    CE%sT    1945 Apr  2 2:00
            1:00    Neth    CE%sT    1977
            1:00    EU    CE%sT

Re: Odd behaviour of timestamptz

From
"hubert depesz lubaczewski"
Date:
On 2/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'll bet you are running in Europe/Amsterdam time zone?  The above is

what about me? i'm in poland, and runing in europe/warsaw time zone. i assume we also had some issues lie this - where can i read about it?

best regards,

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

Re: Odd behaviour of timestamptz

From
Scott Marlowe
Date:
On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote:
> On 2/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>         I'll bet you are running in Europe/Amsterdam time zone?  The
>         above is
>
> what about me? i'm in poland, and runing in europe/warsaw time zone. i
> assume we also had some issues lie this - where can i read about it?

Take a look here:

http://en.wikipedia.org/wiki/Time_zone
http://en.wikipedia.org/wiki/UTC+0:20
http://en.wikipedia.org/wiki/List_of_time_zones

Re: Odd behaviour of timestamptz

From
Tom Lane
Date:
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote:
>> On 2/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I'll bet you are running in Europe/Amsterdam time zone?  The
>>> above is
>>
>> what about me? i'm in poland, and runing in europe/warsaw time zone. i
>> assume we also had some issues lie this - where can i read about it?

> Take a look here:

> http://en.wikipedia.org/wiki/Time_zone

Also, the zic data that Postgres uses can be seen in our CVS:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/data/

The comments in those files are, um, extensive.

            regards, tom lane

Re: Odd behaviour of timestamptz

From
Matteo Beccati
Date:
Hi,

Tom Lane wrote:
> I'll bet you are running in Europe/Amsterdam time zone?  The above is
> correct behavior according to the zic data files (see below).

Yes, for somewhat reason the server was installed using Europe/Amsterdam
as timezone, that's why no other italian user could replicate the issue.

Thanks everyone for the help and explanations!


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com