How to convert Oracle "TIME" to JDBC Time in query?

Question

Oracle doesn't support the TIME datatype, but you can use DATE to store a TIME.

My problem:

select to_date('2009-06-30', 'YYYY-MM-DD') as "DATE",
       to_date('16:31:59', 'HH24:MI:SS') as "TIME"
from dual

yields:

DATE        TIME
2009-06-30  2009-08-01

when I run it through JDBC (in SQuirrel SQL, in fact). "2009-08-01" isn't a very useful time. In my code, I can use ResultSet.getTime(). But how can I get the time in a generic query? Is there a way to cast the result?

Answer 1

The DATE type is stored as a fractional value -- the date as the integer portion, and the time of day as the fractional portion. So, every DATE has the time, which you can extract with something like this.

select TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

Answer 2

If I get the point you could use string Time value:

select to_date('2009-06-30', 'YYYY-MM-DD') as "DATE",
       '16:31:59' as "TIME"
from dual;

String time_str =  ResultSet.getString("Time");
java.sql.Time jsqlT = java.sql.Time.valueOf( time_str);

it should produce the result you need.

Answer 3

Oracle does not have a "Time" datatype. It has a DATE, which is the date and time accurate to the second. It also has a Timestamp with greater accuracy.

When you perform a to_date() call, you get back a...DATE! Depending upon how you have your NLS settings established you can have the text for that date show however you want. The NLS settings control things like date formats, timestamp formats, currency characters, decimal separators, etc. Obviously your NLS settings are defined to show the DATE data as yyyy-mm-dd.

If you're trying to do generic JDBC stuff with Oracle DATEs, you need to specify a "static" value for the "day" portion of the date, and then specify the time as well. Consider this example:

String sql = "Select to_date('1970-01-01 ' || ? ,'YYYY-MM-DD HH24:MI:SS) as MY_TIME from dual";
Connection conn = null; //get Connection from somewhere
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, "16:31:59");
ResultSet rs = stmt.executeQuery();
rs.next(); //get to first element of result set;
java.sql.Time myTime = rs.getTime(1);

The result of myTime will be a java.sql.Time value containing the value for 16:31:59. Remember to keep the day portion some constant value (like the 1970-01-01 above) so that queries with the Time in the where clause will work properly.