Troubles with converting oracle.sql.TIMESTAMPTZ to string value

Question

I try get some data from oracle db, put this data in json and use it in other place, but i have problem with converting timestamptz. Oracle give me timestamp with timezone in string format like "23.10.14 18:34:16,000000 ASIA/NOVOSIBIRSK". Here some piece of my code.

public void loadFromDb(ResultSet resultSet, Connection oc) throws SQLException {
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();
    for (int i = 1; i <= columnCount; i++) {
        Object obj = resultSet.getObject(i);
        if (obj == null)
            continue;
        if (obj instanceof TIMESTAMPTZ) {
            TIMESTAMPTZ ts = (TIMESTAMPTZ) obj;
            if (oc != null) {
                super.setValue(metaData.getColumnName(i), ts.stringValue(oc));
            } else {
                super.setValue(metaData.getColumnName(i), ts.stringValue());
            }
    }
}

and i get this exception

java.sql.SQLException: Conversion to String failed
at oracle.sql.Datum.stringValue(Datum.java:181)

btw, earlier i get timezone in number format like 'XX:XX' and this code not work too, but work this magic super.setValue(metaData.getColumnName(i), ts.stringValue(null))now this way thtows nullpointerexception. Please help me, because i tried all which found in javadocs.

There can be some problems with your jdbc driver + have you read this? http://stackoverflow.com/questions/17559658/oracle-jdbc-retrieving-timestamp-with-time-zone-value-in-iso-8601-format — Multisync, Oct 24 '14 at 05:24
I think that the easiest way to solve the problem is to retrieve string representation of timestamp with time zone (if it's possible in your case) — Multisync, Oct 24 '14 at 05:28
@Multisync maybe i missunderstand you but this my problem - get string representation of timestamp with time zone — mechanikos, Oct 24 '14 at 05:32
I mean select to_char(col_timestamp_with_time_zone, 'format') ... Which format do you need? — Multisync, Oct 24 '14 at 05:34
@Multisync i cant do anything in db side. i just call procedure which return some resultset. thanks for you link, i think that #4(write own toString()) is one possible way for my situation.... — mechanikos, Oct 24 '14 at 05:56
It can be dangerous because Oracle can generate different representations of this type depending on database/session settings. If you call a function which return a cursor you may represent this call as select from table and make a conversation on the oracle side. — Multisync, Oct 24 '14 at 06:06
No, I wasn't right. Seems it's impossible without touching a db: http://stackoverflow.com/questions/13690110/function-return-sys-refcursor-call-from-sql-with-specific-columns Then I would recommend an answer which was awarded +50 because it takes into consideration different formats Oracle may return — Multisync, Oct 24 '14 at 06:17

score 1 · Answer 1 · answered Apr 24 '17 at 06:04

1

The format of TIMESTAMPTZ is well documented. We currently use the following code.

If you are on Java SE 8 and ojdbc8 you can use getObject(int, OffsetDateTime.class). If you use getObject(int, ZonedDateTime.class) you may be affected by bug 25792016.

answered Apr 24 '17 at 06:04

Philippe Marschall

4,452
1
34
52

score 0 · Answer 2 · answered Oct 24 '14 at 05:55

TIMESTAMP is not a string and vice-versa. You need to convert it into a string using TO_CHAR and desired format.

SQL> SELECT SYSTIMESTAMP
  2  FROM   dual
  3  /

SYSTIMESTAMP
---------------------------------------------------------------------------
24-OCT-14 11.14.55.116000 AM +05:30

SQL>
SQL> SELECT TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') tm
  2  FROM dual
  3  /

TM
-------------------------------------
24-OCT-2014 11:14:55.163000 +05:30

SQL> SELECT to_timestamp_tz('24-OCT-2014 11:07:40.348000 +05:30','DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') tm
  2  FROM dual
  3  /

TM
---------------------------------------------------------------------------
24-OCT-14 11.07.40.348000000 AM +05:30

SQL> SELECT TO_TIMESTAMP('24-OCT-2014', 'DD-MON-YYYY')
  2  FROM dual
  3  /

TO_TIMESTAMP('24-OCT-2014','DD-MON-YYYY')
---------------------------------------------------------------------------
24-OCT-14 12.00.00.000000000 AM

Let's validate.

SQL> set serveroutput on;
SQL> DECLARE
  2    tm_stamp  TIMESTAMP;
  3    tm_string VARCHAR2(30);
  4  BEGIN
  5    SELECT SYSTIMESTAMP INTO tm_stamp FROM dual;
  6    dbms_output.put_line(tm_stamp);
  7    SELECT SYSTIMESTAMP INTO tm_string FROM dual;
  8    dbms_output.put_line(tm_string);
  9  END;
 10  /
24-OCT-14 11.17.35.180000 AM
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7


SQL>

So, tm_string cannot store timestamp, since tm_stamp is a string type and not timestamp.

The correct way is to convert it into string explicitly.

SQL> DECLARE
  2    tm_stamp  TIMESTAMP;
  3    tm_string VARCHAR2(50);
  4  BEGIN
  5    SELECT TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') INTO tm_string FROM dual;
  6    dbms_output.put_line(tm_string);
  7  END;
  8  /
24-OCT-2014 11:25:20.060000 +05:30

PL/SQL procedure successfully completed.

SQL>

thanks, but i limited resource of java, because i cant do anything in db side. i just call procedure which return some resultset. — mechanikos, Oct 24 '14 at 05:59

Troubles with converting oracle.sql.TIMESTAMPTZ to string value

2 Answers2