At the moment of daylight saving changes, database adds one hour and magically changes the stored value

[GoogleCodeExporter]

VERSION : 1.3.168

This script will demonstrate a very undesired behavior for any database: I 
write something to the database, but when I read it back in, I end up with 
something different from the value that i put in.

UPDATE temp SET my_timestamp  = ( 
    PARSEDATETIME('2012-10-28 00:00:00 GMT',
    'yyyy-MM-dd HH:mm:ss z', 'en', 'GMT')
) WHERE id  = 1;

CALL formatdatetime(
  SELECT my_timestamp FROM temp WHERE id = 1,
  'yyyy-MM-dd HH:mm:ss z', 'en', 'GMT'
);

I put in   : '2012-10-28 00:00:00 GMT'
I get back : '2012-10-28 01:00:00 GMT'

I put in   : '2012-10-28 00:30:00 GMT'
I get back : '2012-10-28 01:30:00 GMT'

I put in   : '2012-10-28 01:30:00 GMT'
I get back : '2012-10-28 01:00:00 GMT'

(so between 00:00 and 01:00 during that special date, it always adds 1 hour)

Notice that this happens for this special date only! (and probably all the 
other daylight saving dates). When i change it to a random date, it will of 
course work nicely.

I honestly don't know what should be done with this - daylight saving changes 
is a very problematic concept for sure. I stumbled upon this in production, 
where it caused a very nasty problem -- some background job was validating data 
integrity between some crypted and serialized data and its equivalent in the 
database. Of course, as after writing this date to the database and reading it 
back in to validate integrity, integrity check failed for this specific date, 
as H2 had chagned it.

This probably can't be solved in a 100% clean manner, but i think it should at 
least use some sort of a hack to respect the expectation that your database 
returns the same value that you stored it there before. If daylight saving 
changes lose this one hour from before, it probably should still somehow be 
remembered what exactly the used did set it to, and the same thing returned.. I 
hope this is possible to do in your data model.

Original issue reported on code.google.com by san...@zeroturnaround.com on 19 Mar 2013 at 9:42

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Same thing works fine in MySQL:

mysql> UPDATE Project set my_timestamp  = '2012-10-28 00:00:00' where id  = 1;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT my_timestamp from Project where id = 1;
+---------------------+
| my_timestamp        |
+---------------------+
| 2012-10-28 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Original comment by san...@zeroturnaround.com on 19 Mar 2013 at 10:26

[GoogleCodeExporter]

Hi,

This isn't a problem within H2. It's a problem of Java data / time handling, if 
you use PARSEDATETIME. The example you gave for MySQL also works for H2:

drop table project;
create table project(id int, my_timestamp timestamp);
insert into project values(1, null);
UPDATE Project set my_timestamp  = '2012-10-28 00:00:00' where id  = 1;
select * from project;

Original comment by thomas.t...@gmail.com on 20 Mar 2013 at 5:25

• Changed state: Invalid

[GoogleCodeExporter]

Before logging a bug, please use the H2 Google Group or StackOverflow.

Original comment by thomas.t...@gmail.com on 20 Mar 2013 at 5:26