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?

CodePudding user response：

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.