PreparedStatement sets java.util.Date and java.sql.Date without Time information into Mysql dateTime column

Question

How come that java.sql.PreparedStatement#setDate on dateTime mysql column always results in Date format like 2012-10-16 00:00:00 without Time information ? I'm storing Date in a loop like this :

protected Date getIncrementedDateTime() {
    additionalTime += 1000;
    return new Date(System.currentTimeMillis() + additionalTime);
}


preparedStatement.setDate(j, new java.sql.Date(getIncrementedDateTime().getTime());

I debugged the values, but mysql database always have 2012-10-16 00:00:00 values.

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

As per the documentation for java.sql.Date:

A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. [...]

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

Basically, for a datetime column you should probably be using a different type, e.g. Timestamp. (That's not ideal in other ways, but it's likely to be a better fit...)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 16, 2012 at 15:41

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

4 Comments

lisak Over a year ago

Timestamp doesn't go with 'dateTime' column type right ? Incorrect datetime value: '16:16:51' for column ... So that I need to use 'timeStamp' column type and Timestamp data type ?

Jon Skeet Over a year ago

@Sloin: I suspect that Timestamp will actually work fine with a datetime column. It's basically an omission in JDBC here, but Timestamp is probably as close as you can get.

lisak Over a year ago

You're right, java.sql.Timestamp and ps.setTimestamp(j, (Timestamp) value); works fine with 'dateTime' column type

guido.rota Over a year ago

@Sloin Timestamp should work just fine. Try doing this: preparedStatement.setDate(j, new java.sql.Timestamp(getIncrementedDateTime().getTime());

Jay · Accepted Answer · 2012-10-16 15:41:15Z

0

A java.sql.Date is just a date -- no time. Do not confuse it with java.util.Date, which includes time information. You might want to look at java.sql.Timestamp.

answered Oct 16, 2012 at 15:41

Jay

27.6k12 gold badges69 silver badges117 bronze badges

Comments

Zaw Than oo · Accepted Answer · 2012-10-16 15:44:12Z

0

It is base on the data type of your table's column. Try to use TimeStamp or Time according to Database Server.

answered Oct 16, 2012 at 15:44

Zaw Than oo

9,95513 gold badges87 silver badges136 bronze badges

Collectives™ on Stack Overflow

PreparedStatement sets java.util.Date and java.sql.Date without Time information into Mysql dateTime column

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related