Hi All,

I have an issue with timestamp with time zone I don't understand.

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.
However what I don't understand is that there is also a
'second-correction' of 32 in the timestamp value that is not returned in
the time zone.
I guess the returned values should have been '1903-08-06
22:19:32+00:19:32'. But because the time zone specification does not
allow seconds I would expect '1903-08-06 22:19:00+00:19'

Can anyone explain this?
Thanks for any help!

Regards,
Jan

"Jan van der Weijde" <Jan.van.der.Weijde@attachmate.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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend