ResultSetMetaData.getColumnTypeName returns UNKNOWN

Question

I have a query for which the getColumnTypeName returned as UNKNOWN but when getColumnType is invoked it return valid result 92 TIME

I've verified it from here

            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            con= DriverManager.getConnection("jdbc:mysql://localhost/test","root","1234");
            System.out.println("Creating statement...");
            stmt = con.createStatement();
            rs = stmt.executeQuery("select case when id>0 then last_day(date) else makedate(2015,1) end as date from emp.tabDate");
            ResultSetMetaData rsmd=rs.getMetaData();
            System.out.println(rsmd.getColumnTypeName(1));//returns UNKNOWN
            System.out.println(rsmd.getColumnType(1));//returns 92

Is there a way i can get valid Type of the column

Answer 1

As per Java Documentation on Constants of java.sql.Types, the value 92 represents java.sql.TIME.

Your sql statement includes expression and the derived result must be of type 92 i.e. java.sql.TIME. If it is correct, then try to CAST the expression result as TIME and see the result.

Example on CAST:

select cast(
       case when id>0 
                 then last_day(date) 
                 else makedate(2015,1) 
        end as time ) as expr_res 
  from emp.tabDate

Also look into documentation on getColumnTypeName(int). It says,

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