Converting oracle.sql.timestamptz to java.sql.Timestamp

Question

I'm trying to convert the systimestamp of the (Oracle) database to a java.sql.Timestamp

(Part of) the code I have for testing the process is:

try {
    conn = this.getConnection();
    Statement stmt = conn.createStatement();

    stmt.execute("SELECT SYSTIMESTAMP FROM DUAL");

    ResultSet rs = stmt.getResultSet();
    ResultSetMetaData rsm = rs.getMetaData();

    while(rs.next()) {
        TIMESTAMPTZ ts = (TIMESTAMPTZ) rs.getObject(1);
        System.out.println(ts);
        System.out.println(ts.getClass());
        System.out.println(ts.timestampValue());
    }
} catch (SQLException e) {
    e.printStackTrace();
    System.out.println(e);
}

The output I get is:

oracle.sql.TIMESTAMPTZ@1e97f9f
class oracle.sql.TIMESTAMPTZ

and then an exception:
java.sql.SQLException: Conversion to Timestamp failed
at oracle.sql.Datum.timestampValue(Datum.java:283)
at com.sumawest.test.model.UnitTest.testTimezone(UnitTest.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at oracle.jdevimpl.junit.runner.TestRunner.doRun(TestRunner.java:111)
at oracle.jdevimpl.junit.runner.TestRunner.start(TestRunner.java:91)
at oracle.jdevimpl.junit.runner.TestRunner.main(TestRunner.java:43)
java.sql.SQLException: Conversion to Timestamp failed

Does anyone have an idea how I can solve this?

Radim Köhler · Accepted Answer · 2013-06-28 03:11:57Z

5

If you decompile the code you can see that you are calling the timestampValue method of the parent Datum class.

This directly throws the exception (no other code in the method). You need to call the timestampValue method of TIMESTAMPTZ itself passing a Connection.

edited Jun 28, 2013 at 3:11

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

answered Jun 28, 2013 at 2:53

Michael

511 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mithun Sasidharan · Accepted Answer · 2011-12-01 09:35:24Z

0

You could use the to_char function to convert it to a string and then use the value.

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

answered Dec 1, 2011 at 9:35

Mithun Sasidharan

21k10 gold badges38 silver badges53 bronze badges

1 Comment

AlikElzin-kilaka Over a year ago

How does this answer helps converting to java.sql.Timestamp?

Collectives™ on Stack Overflow

Converting oracle.sql.timestamptz to java.sql.Timestamp

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related