Dates in HSQLDB and Oracle

Posted by doubleA on April 26, 2007

I’m currently working on a Java project that uses HSQLDB for local unit testing, but tqa and production run against an Oracle database. My project uses Hibernate and we are using an import.sql script to automatically populate the database for tests. This was working perfect until we did our first deployment to Oracle. All of the date code was broken because of the differences between date formats in the 2 database systems.

I struggled to find a common approach so I could keep my single import.sql statement, but I never found a solution. If you know of one, please let me know because it would simplify my process of configuring test data.

My solution was to create 2 SQL files. The files have the same format except for the date values. I’m manually editing the files when I need to add data, but I’m sure you could create a script or batch file to do the replacement automatically. My manual edits consist of a lote of find/replace, but it only takes a couple of minutes. As the test data gets larger, I’m sure I will be trying to automate this.

So here is my quick synopsis of date formats between HSQLDB and Oracle:
HSQL
Simple dates can be constructed as a string such as:
'2000-01-01'

However, for my sample code, I needed dates relative to the current date. In order to do that, I had to concat a string together. As we’ll see later, Oracle makes this much easier. Nonetheless, here is a sample that will create a date that is 60 days prior to today. The nice part about this approach is that HSQLDB takes care of adjusting the month and year values so you can just subtract whatever number you want from current_date.
year(current_date) || '-' || month(current_date) || '-' || (dayOfMonth(current_date)-60)

Oracle
Simple cates can be created as:
TO_DATE('2000-01-01', 'YYYY-MM-DD')
or this way if you use the standard Oracle format of YYYY-MMM-DD:
'2000-JAN-01'
For dates relative to day, simply use the following code. Again, the database takes care of adjusting the month and year values.
TO_CHAR(sysdate-60, 'DD-MON-YYYY')

This entry was posted on April 26, 2007 at 4:50 pm and is filed under Java Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

7 Responses to “Dates in HSQLDB and Oracle”

Cellphones said

June 7, 2007 at 6:17 pm
There are several cel phone on the ton
for willpower place trades. In herbal, cheat contrary gloves
to bond any exhibit that they improve.

Reply
anonymous said

March 5, 2008 at 4:48 pm
Why don’t you write your own TO_DATE() function and create an alias in HSQL. If you make it mimic the Oracle TO_DATE() function then you can use the same script for both databases. You only need the single parameter version if you specify your dates using the Oracle default date format.

Reply
- Anonymous said
  
  September 1, 2009 at 3:58 am
  You don’t need to write your own alias:
  
  select date ‘2009-01-01′ from dual;
  
  Will work on both oracle & hsqldb (you’ll have to create a table called dual on hsqldb, though
  
  Reply
  - Anonymous said
    
    September 1, 2009 at 4:26 am
    select (date ‘1982-02-02′) + 1 day from dual;
    
    Is also cross-platform…(At least on hsqldb 1.9.0rc4 – not earlier versions)
zombiewoof said

October 28, 2008 at 6:21 am
You could try placing these commands in the import.sql. They make the Oracle default date format the same as that of HSQLDB so the same strings will insert a date for either DB. Obviously they should be commented out when using HSQLDB.

alter session set nls_date_format=’yyyy-mm-dd’;
alter session set nls_timestamp_format=’yyyy-mm-dd HH24.MI.SSXFF’;

Reply
Harold said

February 24, 2009 at 6:48 am
Thanks! Both suggestions are good. If you want to use the first suggestion and create user function, here is an example of the code. This class should be on the HSQLDB server classpath.

package com.myproject;
public class Date {
public static String toDate(String value, String format) {
return value;
}
public static String toTimestamp(String value, String format) {
return value;
}
}

In HSQLDB, use this syntax to define the functions when you create the database:

create alias to_date for “com.myproject.Date.toDate”;
create alias to_timestamp for “com.myproject.Date.toTimestamp”;

Here are examples of insert statements that work both in Oracle and HSQLDB.

insert into ledger (ledger_time)
values(to_timestamp(‘2009-02-24 08:20:57′,’YYYY-MM-DD HH:MI:SS’));
insert into holidays (holiday_date)
values(to_date(‘2009-07-04′,’YYYY-MM-DD’));

Reply
Danilo Akamine said

September 22, 2009 at 12:58 pm
an interesting post

Reply