I'm puzzled by the results I get when using TIMESTAMP and AT LOCAL when it comes to time-zone/daylight saving conversions.
I'm not sure if this is a bug or not. Maybe I'm just doing the wrong assumptions. In case please help me to better understand what is wrong.
In one of the columns of the table I have to query there's a number representing a timestamp. For example: 1228086000
I'm located in Italy, current timezone is GMT+2 (due to daylight saving, otherwise it would be GMT+1)
According to website http://www.epochconverter.com that timestamp corresponds to:
GMT: Sun, 30 Nov 2008 23:00:00 GMT
My time zone: 01 dec 2008 00:00:00 CET GMT+1
It is important to notice that the local time is GMT+1, because that date belongs to the range in which no daylight saving is applied.
Now I tested the following statements with the corresponding results (-->):
(A) TIMESTAMP(1228086000) --> 2008-11-30 23:00:00.0
(B) TIMESTAMP'2008-11-30 23:00:00+0:00' --> 2008-11-30 23:00:00.0
(C) TIMESTAMP('2008-11-30 23:00:00+0:00') --> 2008-11-30 23:00:00.0
(A1) TIMESTAMP(1228086000) AT LOCAL --> 2008-11-30 23:00:00.0
(B1) TIMESTAMP'2008-11-30 23:00:00+0:00' AT LOCAL --> 2008-12-01 01:00:00.0
(C1) TIMESTAMP('2008-11-30 23:00:00+0:00') AT LOCAL --> 2008-11-30 23:00:00.0
When "AT LOCAL" is not used, I get the same result for A,B,C.
I would expect A1,B1,C1 to be different from A,B,C and all look the same, but it is not the case.
As it regards A1 and C1 it seems that "AT LOCAL" has no effect.
B1 is different, but still it is wrong. In fact I would expect B1 to return 2008-12-01 00:00:00.0, because on 1st december there's no daylight saving (GMT+1).
Instead it seems that the timestamp is converted to the current time zone (GMT+2).
Am I doing something wrong? If yes, what should I do to go from that number (1228086000) to the correct local date-time (2008-12-01 00:00:00.0) ?
Log in to post a comment.