Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#1295 TIMESTAMP and AT LOCAL behavior

current-release
closed-fixed
Fred Toussi
1
2013-04-11
2013-04-11
Paolo Bassino
No

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

Discussion

  • 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 hsqldb.org/support 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