getDateTime from ResultSet java

Question

I have two columns which store DateTime values in a MySql database,

When I try to get them from a ResultSet in Java there is no option: getDateTime()

Should I use getDate() or wouldn't that work?

Thanks in advance

Pulsate

Answer 1

Using getDate() only returns a Date, so in your case if you want both date and time use getTimestamp(String columnLabel) - That should work, also replace String columnLabel with the actual column name in the database.

Schoolmarm

getTimestamp is Timestamp but the field is DateTime, which is throwing compilation error – Unglue

Timestamp ts1 = rs.getTimestamp(String columnLabel); DateTime dt1 = new DateTime(ts1); you may have to mess with timezone though if you do that, so check your times. DateTime dt1 = new DateTime(ts1,DateTimeZone.UTC); – Someday

Answer 2

You can use ResultSet.getTimeStamp()

Paternoster

Answer 3

Prefer timestamp over datetime in MySQL

First, if the datetime in your database is to represent a point in time, use the timestamp datatype in MySQL for that, not datetime. A datetime is open for interpretation into any time zone the reader or the program reading it happens to use. This can lead to errors that are hard to debug. The timestamp datatype behaves differently in various RDBMSs. In MySQL it makes sure that dates and times are in UTC, thus excluding faulty interpretation into any other time zone. It’s safer. If the dates and times are a little harder for users in your own time zone to read in the database, it’s worth it.

java.time

    PreparedStatement ps = yourDatabaseConnection.prepareStatement("select your_datetime_col from your_table;");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            OffsetDateTime dateTime = rs.getObject("your_datetime_col", OffsetDateTime.class);
            // do something with dateTime
        }
    }

Use ResultSet.getObject() for retrieving date and time from the database into a type from java.time, the modern Java date and time API. The classes java.sql.Timestamp, java.sql.Dateand java.util.Date mentioned or implied in the question and the other answers are all poorly designed and long outdated, so I recommend the modern API instead. It requires a JDBC 4.2 compliant driver. Most of us have that, and one has been out for MySQL for very many years. SO I expect that you are fine.

If you cannot change the datatype in MySQL, use LocalDateTime in Java for retrieving values from your datatime column. It goes in the same way as in the code above.

To answer your concrete question

Should I use getDate() or wouldn't that work?

No it would not. getDate() gives you a java.sql.Date holding only the date part from the database, the time of day part will be missing. As I said, java.sql.Date is also poorly designed — a true hack on top of the already poorly designed java.util.Date — so you shouldn’t want that anyway.

Links

• Java Best Practice for Date Manipulation/Storage for Geographically Diverse Users
• My answer to Getting the date from a ResultSet for use with java.time classes
• Oracle tutorial: Date Time explaining how to use java.time.

Barker

Answer 4

Or with a typecast from date to string:

resultSet.getDate(1).toString());

will return:

2014-02-18

DB field data: "2014-02-18 00:00:00.000"

Ursulaursulette

getDate() doesn't give you the time part. This is not the best if you want the time part of the mysql datetime. – Andean

Answer 5

Converting java.sql.Timestamp to java.util.Date.

java.util.Date date = rs.getDate(index_position); // O/P: DD:MM:YYYY

java.sql.Timestamp timestamp = rs.getTimestamp(index_position); // O/P: DD:MM:YYYY HH:mm:ss
java.util.Date date = new java.util.Date(timestamp.getTime());

If you timestamp to date and convert it to java.sql.Timestamp then it results to DD:MM:YYYY 00:00:00 as the date will not cover time. So, it picks default as 00:00:00.

SQL TimeStamp  : 30.12.2020 13.40.50
Java TimeStamp : 30.12.2020 13.40.50

SQL TimeStamp  : 30.12.2020 13.40.50
Java Date      : 30.12.2020 00.00.00
Java TimeStamp : 30.12.2020 00.00.00

Example to get records based on ResultSetMetaData for CSV Report:

public static void getTestTable(Connection con) throws SQLException {
    String sql = "select ID, INSERTDATE, TO_CHAR(INSERTTIME,'DD.MM.YYYY HH24:MI:SS') as String_Time, INSERTTIME, MSG from TEST_TABLE group by ID, INSERTDATE, INSERTTIME, MSG";
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    if (rs != null) {
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int noOfColumns = resultSetMetaData.getColumnCount(); // For iteration
        System.out.println("No of Cloumns Query Returns: "+ noOfColumns);
        HashMap<String, ArrayList<?>> matrixTable = getMatrixMetadata(resultSetMetaData);
        System.out.println("MatrixTable: "+matrixTable);
        ArrayList<String> dataTypeList = (ArrayList<String>) matrixTable.get("ColumnTypeName");
        int rowCount = 0;
        while (rs.next()) { // CSV Report
            for (int columnIndex = 1; columnIndex <= noOfColumns; columnIndex++) {
                // int id = rs.getInt("ID");
                String value = getMatrixValue(rs, dataTypeList, columnIndex);
                System.out.print(value);
                if (columnIndex < noOfColumns) {
                    System.out.print(",");
                }
            }
            System.out.println();
            rowCount++;
        }
        System.out.println("Result FetchSize(Total Columns):"+rs.getFetchSize()+" = Rows*Columns:["+rowCount+"*"+noOfColumns+"]");  
    }
}
static HashMap<String, ArrayList<?>> getMatrixMetadata(ResultSetMetaData meta) throws SQLException {
    int columnsCount = meta.getColumnCount();
    ArrayList<String> columnList = new ArrayList<String>();
    ArrayList<String> dataTypeNameList = new ArrayList<String>();
    ArrayList<Integer> dataTypeIdList = new ArrayList<Integer>();
    HashMap<String, ArrayList<?>> returnHashMap = new HashMap<String, ArrayList<?>>();
    for (int i = 1; i <= columnsCount; i++) {
        columnList.add(meta.getColumnName(i));
        dataTypeIdList.add(Integer.valueOf(meta.getColumnType(i)));
        dataTypeNameList.add(meta.getColumnTypeName(i));
    }
    returnHashMap.put("ColumnName", columnList);
    returnHashMap.put("ColumnTypeId", dataTypeIdList);
    returnHashMap.put("ColumnTypeName", dataTypeNameList);
    return returnHashMap;
}
static DecimalFormat DECIMAL_FORMAT = (DecimalFormat) NumberFormat.getInstance(Locale.ENGLISH);
static { // https://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html
    DECIMAL_FORMAT.applyPattern("######.###"); // "#,#00.0#" → 1,234.56 
}
public static String getMatrixValue(ResultSet rs, ArrayList<String> dataTypeNameList, int pos) throws SQLException {
    String retval;
    String columnTypeName = dataTypeNameList.get(pos - 1);
    //int type = dataTypeIdList.get(pos - 1);
    //if (type == Types.DECIMAL || type == Types.DOUBLE || type == Types.FLOAT || type == Types.NUMERIC || type == Types.REAL) {
    if (columnTypeName.equalsIgnoreCase("NUMBER")) {
        double doubleValue = rs.getDouble(pos);
        if (rs.wasNull()) {
            retval = null;
        } else {
            retval = "[N]"+DECIMAL_FORMAT.format(doubleValue);
        }
    } else if (columnTypeName.equalsIgnoreCase("DATE")) {
        java.util.Date date = rs.getDate(pos);
        if (rs.wasNull()) { // Checks last column read had a value of SQL NULL.
            retval = null;
        } else {
            retval = "[D]"+formatDate(date, "dd.MM.yy");
        }
    } else if (columnTypeName.equalsIgnoreCase("TIMESTAMP")) {
        java.sql.Timestamp timestamp = rs.getTimestamp(pos);
        if (rs.wasNull()) {
            retval = null;
        } else {
            java.util.Date date = new java.util.Date(timestamp.getTime());
            retval = "[T]"+formatDate(date, "dd.MM.yyyy HH:mm");
        }
    } else { // VARCHAR2
        retval = "[S]"+rs.getString(pos);
    }
    return retval;
}
public static String formatDate(Date aDate, String formatStr) {
    DateFormat dateFormat = new SimpleDateFormat( formatStr );
    //dateFormat.setCalendar(Calendar.getInstance(TimeZone.getTimeZone("IST")));
    return dateFormat.format(aDate);
}

No of Cloumns Query Returns: 5
MatrixTable: {ColumnName=[ID, INSERTDATE, STRING_TIME, INSERTTIME, MSG], ColumnTypeId=[2, 93, 12, 93, 12], ColumnTypeName=[NUMBER, DATE, VARCHAR2, TIMESTAMP, VARCHAR2]}
[N]1,[D]30.12.20,[S]30.12.2020 00:40:50,[T]30.12.2020 00:40,[S]Insert1
[N]2,[D]30.12.20,[S]30.12.2020 13:40:50,[T]30.12.2020 13:40,[S]Insert2
Result FetchSize(Total Columns):10 = Rows*Columns:[2*5]

SQL queries involved are for Table creation and multi record insertion: ^{Oracle Multi insert}

CREATE TABLE SCHEMA7.TEST_TABLE 
(
    "ID" NUMBER, 
    "INSERTDATE" DATE, 
    "INSERTTIME" TIMESTAMP (6), 
    "MSG" VARCHAR2(120 BYTE) 
);

INSERT INTO SCHEMA7.TEST_TABLE (ID, INSERTDATE, INSERTTIME, MSG) VALUES ('1', TO_DATE('30-DEC-2020', 'DD-MON-RR'), TO_TIMESTAMP('30-DEC-2020 12.40.50.00 AM', 'DD-MON-RR HH.MI.SS.FF AM'), 'Insert1');
INSERT INTO SCHEMA7.TEST_TABLE  (ID, INSERTDATE, INSERTTIME, MSG) VALUES ('2', TO_DATE('30.12.2020', 'DD.MM.YYYY'), TO_TIMESTAMP('30.12.2020 13.40.50','dd.mm.yyyy hh24.mi.ss'), 'Insert2');

Ghat

Answer 6

I am using this:

public static Date getDate(ResultSet rs, int col, Date def)
{
    try
    {
        Timestamp ts = rs.getTimestamp(col, Calendar.getInstance());
        if(!rs.wasNull())
        {
            return  new Date(ts.toInstant().toEpochMilli());
        }
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    return def;
}

Impletion