Re: Odd behaviour of timestamptz

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

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

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

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

On 2/21/07, Michael Glaesemann <grzm(at)seespotcode(dot)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

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Matteo Beccati <php(at)beccati(dot)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

On 2/21/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote:
> On 2/21/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:
> On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote:
>> On 2/21/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

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