Question 113326 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – JDBC DATETIME Type

I have a DATETIME column in a MySQL database. I use ResultSet metadata to retrieve the column type and it returns TIMESTAMP which is incorrect. How can I get the correct java.sql.SQLType value for this column?

I will try to be more specific. I define the structure of my database in Java code. For instance;

Column TITLE = new Column(JDBCType.VARCHAR).size(100).title("Created");

And then my small validator code creates this column (assume the table definition is there). Then, later if I modify this code as

Column TITLE = new Column(JDBCType.VARCHAR).size(200).title("Created");

My small validator alters the column size to 200. To do that, I retrieve the metadata as

DatabaseMetaData metaData = connection.getMetaData();

And then access the column properties as

ResultSet resultSet = metaData.getColumns(getCatalog(), schema, table, columnName);

This returns me JDBCType enumeration. Let’s assume that I run this query on a MySQL DATETIME column. I do know that in Java, there is no such thing and its equivalent is java.sql.Timestamp class. But in MySQL, DATETIME is not TIMESTAMP.

How can I differentiate between MySQL DATETIME and MySQL TIMESTAMP in Java code?

Tags:

2

Answers


  1. 0

    Mapping SQL types to Java types

    The values in java.sql.Types represent SQL standard types, not Java types. For a modern enum representing the same SQL standard types, see java.sql.JDBCType, an implementation of java.sql.SQLType.

    The value java.sql.Types.TIMESTAMP (and corresponding enum java.sql.JDBCType.TIMESTAMP) represents the SQL standard type TIMESTAMP, a date with time-of-day but lacking the context of an offset-from-UTC or time zone.

    The MySQL DATETIME type maps to java.sql.Types.TIMESTAMP/java.sql.JDBCType.TIMESTAMP. The appropriate class in Java for this type is java.time.LocalDateTime.

    myResultSet.getObject( … , LocalDateTime.class )

    LocalDateTime

    To quote the MySQL version 8 reference:

    The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD hh:mm:ss’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

    This data type DATETIME represents a date with a time-of-day. Crucially, no indication of time zone or offset-from-UTC is present.

    So this type cannot represent a moment, a specific point on the timeline. Be careful when assigning a column this type; be sure this suits your intentions. For example, this type should not be used when recording the moment of a past event.

    👉🏾 The Java class java.time.LocalDateTime matches a MySQL DATETIME column.

    JDBC 4.2, 4.3, and later require support for the java.time classes such as LocalDateTime.

    LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

    Writing to the database.

    myPreparedStatement.setObject( … , ldt ) ;

    Avoid legacy date-time classes

    ⚠️ Beware of java.sql.Timestamp, never use. That terrible class is now legacy, supplanted years ago by the modern java.time classes defined in JSR 310.

    The Timestamp class cannot represent a MySQL DATETIME value properly as the class includes offset/zone info which the database column lacks.

    Login or Signup to reply.
  2. 0

    From a generic JDBC perspective, you can use the column TYPE_NAME from DatabaseMetaData.getColumns(...), this should return:

    TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified

    In other words, it should contain the name of the data type as used in the data source (MySQL in your case should return DATETIME).

    Note: I haven’t actually verified this, my answer is purely based on generic JDBC requirements.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search