Help save net neutrality! Learn more.

#1295 TIMESTAMP and AT LOCAL behavior


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 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) ?
Thank you.


  • Fred Toussi

    Fred Toussi - 2013-04-11

    This is a help request. You should have asked at the Help Forum.
    There was a bug in one of the coversions which ha been fixed. Otherwise, AT LOCAL is not aware of daylight saving and uses the current time zone for conversion.

    Please try with the latest snapshot jar from the and ask any questions in the forum.

  • Fred Toussi

    Fred Toussi - 2013-04-11
    • assigned_to: nobody --> fredt
    • priority: 5 --> 1
    • status: open --> closed-fixed

Log in to post a comment.