Re: Odd behaviour of timestamptz

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matteo Beccati <php(at)beccati(dot)com>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Odd behaviour of timestamptz
Date: 2007-02-21 14:35:47
Message-ID: 13129.1172068547@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2007-02-21 14:42:26 Re: postgresql vs mysql
Previous Message Richard Huxton 2007-02-21 14:24:13 Re: REVOKE ALL