Is there a way to differentiate in Oracle/JDBC between DATE and TIMESTAMP?

Question

I have a Java application where the user builds their SQL select statement on the screen. Once they enter the select, I analyze with JDBC the column types.

For Oracle, the JDBC equivalent of type DATE is java.sql.Types.Timestamp, as DATE includes the time.

The problem is that I need to format the result set depending on the column type. If it's DATE, I need to format the value YYYY-MM-DD. If it's TIMESTAMP, I need to format the value YYYY-MM-DD HH:MM:SS.

What I need is to differentiate in JDBC between DATE and TIMESTAMP results. Is there a way to achieve this?

Sample code:

        String sql = "select date_col, timestamp_col from some_table";
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData meta = rs.getMetaData();
        int count = meta.getColumnCount();

        for (int i=1;i <= count;i++) {
            int type = meta.getColumnType(i);
            System.out.println(type);
        }

This prints twice 93, which is java.sql.Types.Timestamp.

CREATE TABLE "DB1"."SOME_TABLE" 
   ("SOMENUM" NUMBER(9,0), 
    "DATE_COL" DATE, 
    "TIMESTAMP_COL" TIMESTAMP (6), 
    "ACCOUNT" NUMBER(9,0), 
    "BALANCE" FLOAT(126)) 

Note: I'm running this on Wildfly 14

I tried to cast the sql Connection to OracleConnection (to get the oracle.sql types) and Wildfly throws:

org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8 cannot be cast to oracle.jdbc.OracleConnection

Answer 1

You can use ResultSetMetdata#getColumnTypeName to get actual DB type name

Retrieves the designated column's database-specific type name.

Return type name used by the database. If the column type is a user-defined type, then a fully-qualified type name is returned.