标签云

微信群

扫码加入我们

WeChat QR Code

To make a JDBC query I need to pass date to it. The date is kept in Date field type of PostgreSql database, which represents specific day without any time.

As I need only date, I decided to use specific object which represent only date without time, which is LocalDate from Joda-Time package. I thought it is important because if I used DateTime object, it would carry redundant time data as well as it might lead to bugs at end of daylight saving time when the clock are put backward one hour (though the situation is unprecedentedly rare, it's not impossible).

But when I started trying to square LocalDate object with accepted arguments of preparedStatement.setDate method, I didn't find a proper way to do it.

setDate accepts java.sql.Date as parameter. And the only option to construct java.sql.Date object is to pass it time in milliseconds.

But this defeats all the purpose of using LocalDate from Joda-Time package, as on this conversion we get back to milliseconds and, while these conversions happen, clock may be put back one hour and change the date to the previous date.

So, now I have this line in my code:

preparedStatement.setDate(1, new java.sql.Date(localDate.toDate().getTime()));

But is this the best way to convert LocalDate to accepted by setDate format?

Are my concerns related to daylight saving time and corresponding clock-shifts justified?

Is there a better way to pass date (and only date without time) to JDBC preparedStatement?


And what are the semantics at the db side? If it's instant and not date, you're fried anyway.

2018年06月19日17分03秒

MarkoTopolnik As I'm getting from the table it's just date withouth time (as it takes 4 bytes, not 8 bytes as DateTime types).

2018年06月19日17分03秒

It should be safe to use your technique because all the timezone issues should be taken into account by LocalDate#toDate.

2018年06月20日17分03秒

MarkoTopolnik But what if we create an instance of LocalDate, then we call toDate() on it, then clock is rewinded one hour back and the day becomes the previous one, then getTime() is called (it's still a previous day), and then java.sql.Date is constructed (it's still a previous day). So we end up with the previous day. I understand that I can use UTC with no daylight saving times. But still, I see no purpose in using any time-related values, when I don't need time at all (but just date).

2018年06月19日17分03秒

That is not what can happen. The millisecond instant you have is context-independent: it uniquely relates to a timezone valid at that point in time. The only real trouble you can get is when two instants map to the same time label (events when the clocks are turned backwards so the same time labels repeat). There have actually been some clarifications as to the exact semantics in those cases, but you shouldn't be affected by them, anyhow.

2018年06月20日17分03秒

Hopefully JDBC will also take advantage of this and start accepting LocatDate object as parameter to setDate!

2018年06月19日17分03秒

ovgolovin Indeed, you can exchange java.time objects directly with JDBC 4.2 and later. Use PreparedStatement::setObject and ResultSet::getObject rather than setDate/getDate. See my Answer on this same Question for details. myPreparedStatement.setObject( … , localDate ) and myResultSet.getObject( … , LocalDate.class ).

2018年06月20日17分03秒

I don't believe this will work for Joda LocalTime

2018年06月20日17分03秒

You're confusing Java 8 LocalDate with Joda LocalDate. The question is about Joda LocalDate.

2018年06月19日17分03秒

Is toDateTimeAtStartOfDay a typo? Did you mean the withTimeAtStartOfDay method on the Joda-Time DateTime class?

2018年06月20日17分03秒

No, it is not a typo. Before using it I set startDate to, let's say, "2015-01-01' and in it was persisted to database as "2014-12-31". This now works because the conversion to LocalDate is done as it should.

2018年06月19日17分03秒

So can you link to the doc? I can't find that method.

2018年06月19日17分03秒

What does using toDateMidnight solve?

2018年06月20日17分03秒

JodaTime-DateMidnight

2018年06月19日17分03秒

This still casts date to a specific instance of the moment. And it may happen that after this conversion the current moment will pass the end of DST period and the clock will be rewound one hour back. After that the instance of the moment we saved will represent 23:00 of the previous day. That is the problem. And I expected there should be a way to work with dates without resorting to time instances.

2018年06月20日17分03秒

Also, what additionally it offers to what toDate method does?

2018年06月20日17分03秒

The midnight-related classes and methods have been deprecated in latest versions of Joda-Time. Instead call method withTimeAtStartOfDay. The first moment of the day is not always 00:00:00.0.

2018年06月19日17分03秒