2

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?

Improve this question
6
  • TIMESTAMP is the correct type. You are thinking of the MySQL datatype, and not of the way JDBC (and the SQL standard) define types. Commented Oct 6, 2022 at 16:22
  • What makes you think it's incorrect btw? Commented Oct 6, 2022 at 16:24
  • In any case, if you want to distinguish this, you probably need to use ResultSetMetaData.getColumnTypeName, which should return the database-specific name of the column (though I haven't verified if MySQL Connector/J returns the correct value here) Commented Oct 6, 2022 at 16:30
  • @MarkRotteveel TIMESTAMP is the correct type in Java but not in MySQL. I need to know the exact column type (or simply its column name) as it appears in MySQL which is DATETIME in my case. Commented Oct 7, 2022 at 22:23
  • 1
    @OmerHalit I don't normally use MySQL, but - from a generic JDBC perspective - you should be able to get that information from DatabaseMetaData.getColumns, column TYPE_NAME (and for its name, COLUMN_NAME). Commented Oct 8, 2022 at 8:26

2 Answers 2

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Had this answer typed up, you beat me to the punch. One addition: As you can see here, TIMESTAMP is the right type, or at least, the only one that's available, really. JDBC and its types were designed before java.time, unfortunately. I don't actually know if .getObject(x, LDT.class) works for the mysql JDBC driver. Let's hope so!
@rzwitserloot I added a note that any JDBC 4.2+ driver must support java.time. Thanks for your comments — motivated me to add a few more details.
I understand this. I have no problem in writing or reading. Why is DATETIME column type is fetched as TIMESTAMP in ResultSet metadata? That is incorrect and doesn't let me differentiate between those two types.
@OmerHalit See the Comment by rzwitserloot. The computing industry, including the early versions of Java, never invented a proper date-time handling framework until java.time (and its predecessor Joda-Time). So in the old days, java.sql.Timestamp was all they had to use as a hack to hold your DATETIME MySQL value. That was a terrible, flawed hack — I imagine it created much confusion and failed software. Fortunately, we have java.time now.
@OmerHalit (A) You lost me on your last sentence. There is no DATETIME class in Java. So I do not now what you mean by "attempts to alter it to DATETIME which is already a DATETIME". (B) I don't know how to be anymore clear: java.sql.Timestamp was mapped to DATETIME as a hack, because Java failed to include any class built to represent a date with time but without zone/offset. All I can say is: Use only java.time classes for your date-time work. Never use java.sql.Timestamp.
|
1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.