Re: Question about timestamp with time zone

"Jan van der Weijde" <Jan(dot)van(dot)der(dot)Weijde(at)attachmate(dot)com> writes:
> When I insert a time stamp value '1903-08-07 00:00:00+02' into a table
> and next select it again using psql I get '1903-08-06 22:19:32+00:19'.
> I'm located in The Netherlands and before 1940 there was a so called
> Amsterdam Time that is UTC + 20. So that more or less explains the
> +00:19 that is returned.

Well, actually, what I see in the zic database is

# 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

So converting midnight GMT+2 to the reported result seems right. I
think the problem is that the timestamp output function isn't expecting
there to be any residual seconds in the zone GMT offset, and so doesn't
bother to display it. As you say, the correct display would be
'1903-08-06 22:19:32+00:19:32'.

If we fix this we'd also have to fix timestamp_in to be willing to
accept such strings, too.

regards, tom lane