get time information from oracle DATE field

[mangesh lele]

i have a table with a DATE field. i want to retrieve the time information from the value stored in this field and wrap it into a GregorianCalendar object. when i use lResultSet.getDate(), it returns a java.sql.Date object having only date value but the timestamp is set to 00:00:00 (normalized, i think, as per jdk specs). is there any way i can get both the date and timestamp into my GregorianCalendar object?

[VIJAY Yadlapati]

If resultSet is not returning the Time of Date, you can change that database column to a data type that supports long, and store the time in milliseconds (See Calendar.getInstance().getTimeInMillis()) in it.

[Wayne L Johnson]

You shouldn't have to change the Database column, assuming that the DATE field has a time component to it (most databases act this way). All you have to do is use resultSet.getTimestamp() and you'll have a java.sql.Timestamp instance. From there it's easy to get the date AND time into a GregorianCalendar.

[Maulin Vasavada]

Hi all
Actually, oracle stores time info in the DATE type. Its just that how to get it. I use following convention,
If the column name is - start_time (for something) then I run query like,
"select to_char(start_time,'mm/dd/yyyy hh:mm AM') as StartTime from myTable";
and then do rs.getString("StartTime"); and format the date with something like,

private String[] getDateTime(String date) { // format: mm/dd/yyyy hh:mm AM
                System.out.println("Formatting date:"+date+":");
                String[] retVal = new String[2];
                StringTokenizer stk = new StringTokenizer(date," ");
                retVal[0] = stk.nextToken();
                retVal[1] = stk.nextToken()+" "+stk.nextToken();
                return retVal;
        }

The code above is specific to my case but still you get the idea you know...
Regards
Maulin

[mangesh lele]

thanks a lot u guys
actually changing the database column to varchar2 is the logical solution, but i wanna try keeping it as DATE first.
there are few more restrictions, since i am working on a generic framework.
i cant change the select query, since it is created dynamically from the list of fields required.
also i think getTimeStamp will just return the time and lose the date, so cant be applied to generic code.
is there any way to wrap the whole DATE field information in GregorainCalendar?

[Wayne L Johnson]

A "java.sql.Timestamp" contains both the date and the time. If you want to get the information into a GregorianCalendar, this will work:

import java.sql.*;
import java.util.Calendar;
...
Connection conn = ... ; // database connection
Statement  stmt = null; // Statement
ResultSet  rset = null; // ResultSet
try {
   Calendar cal = Calendar.getInstance();
   System.out.println("CAL: " + cal.getTime());
   stmt = conn.createStatement();
   rset = stmt.executeQuery(" ... ");
   if (rset.next()) {
      Timestamp ts = rset.getTimestamp("MyDateColumnName");
      cal.setTimeInMillis(ts.getTime());
   }
   System.out.println("CAL: " + cal.getTime());
} catch ...

The variable "cal" will reference a "GregorianCalendar" instance and it will be set with both the date and the time from the database.
NOTE: The "getTime()" method in "java.util.Calendar" actually returns a "java.util.Date", and when it prints out you'll see both the date and the time.
[ November 07, 2003: Message edited by: Wayne L Johnson ]