Date type in H2 vs Oracle

I often use H2 to locally develop code that is supposed to work on Oracle. Yes, yes, I can hear all the 'Why don't you work on production-like environment?!'. Well, I do it because I'm lazy. When the development configuration has no dependency on external infrastructure then every new developer or tester can simply do: git clone ... && mvn test && mvn jetty:run and it just works. Immediately. Without bothering me with questions about the setup, configuration, passwords etc. CI server can build each new branch simultaneously. Local tests run quickly as there is no network communication. It's simply really convenient.

Of course, it has its price. Compatibility. Most applications are really simple and doesn't need any vendor-specific features. But sometimes...

create table my_table (my_column date);
insert into my_table values (timestamp '2013-01-23 13:23:34');
select my_column from my_table;

So what's the result of the select statement?

oracle: January, 23 2013 13:23:34+0000
H2:     2013-01-23

What happened with time? Let's see more details

select cast(my_column as timestamp) from my_table;

oracle: January, 23 2013 13:23:34+0000
H2:     2013-01-23 00:00:00.0

Yep, the time is silently truncated. Oracle Date stores time as opposed to H2, mysql, postgres and probably most others. And this will affect all frameworks you use: jdbc, dbunit, hibernate etc. So, if possible, use Timestamp type or design your application in a way it doesn't matter.

Tested databases (thanks to sqlfiddle.com):

• Oracle 11g R2
• H2 1.3.171
• MySql 5.1.61
• PostgreSQL 9.3.1