Converting between oracle.sql.TIMESTAMPTZ and standard JDBC classes for DbUnit

Question

I'm running Oracle 10g and have columns with Type_Name

TIMESTAMP(6) WITH TIME ZONE

When inflated into java classes they come out as

oracle.sql.TIMESTAMPTZ 

But DbUnit can't handle converting Oracle specific classes to Strings for writing to XML. I'm wondering if there's any easy way for me to convert (say, in my SELECT statement somehow) from these Oracle specific timestamps to something in java.sql.

Answer 1

I haven't had to deal with this problem exactly, but I presume that having it come through as a string from the SELECT query would be fine.

You could use the to_char function. To convert it to a string. e.g:

SQL> select to_char(systimestamp, 'IYYY-MM-DD HH24:MI:SS.FF TZD') as d from dual;

D
----------------------------------
2008-10-21 17:00:43.501591

This would then be seen by your program as a string. TZD includes timezone information (of which there is none in this example)

Later, this could then be parsed by Java using the SimpleDateFormat class.

Alternatively, the oracle.sql.TIMESTAMPTZ class has a method called dateValue that returns a java.sql.Date class.