How to convert from java.sql.Date to ZonedDateTime

Question

I am trying to convert from java.sql.Date to ZonedDateTime. This is the code I am using

ZonedDateTime.from(new java.util.Date(result.get(0).getTime()).toInstant())

However, it leads to the following error -

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.time.DateTimeException: Unable to obtain LocalDateTime from TemporalAccessor: 2016-09-29T18:30:00Z of type java.time.Instant

Is there a way to do this conversion?

Answer 1

tl;dr

ZonedDateTime zdt =
    myJavaSqlDate.toLocalDate()
                 .atStartOfDay( ZoneId.of( "America/Montreal" ) ) ;

Details

Do not mix the old date-time classes with java.time. Avoid the old classes. So your call to java.util.Date is not helpful.

Know that java.sql.Date represents a date-only value, with no time-of-day and no time zone. Unfortunately it actually does have a time-of-day and a time zone, but we are supposed to ignore those facts. This is a poorly-designed hack of a class. Use it only when you do not yet have a JDBC driver that supports JDBC 4.2 and the java.time types.

So when you must use java.sql.Date convert its value to a LocalDate. The LocalDate class represents a date-only value without time-of-day and without time zone.

To convert to/from java.time, look to new methods added to the old classes.

LocalDate ld = myJavaSqlDate.toLocalDate();

Going the other direction.

java.sql.Date myJavaSqlDate = java.sql.Date.valueOf( ld );

If your goal is getting a date-time value from a date-only value, you will need to specify a time-of-day. If you want the first moment of the day, let java.time determine that time-of-day for you. The time 00:00:00 is not always the start of the day because of anomalies such as Daylight Saving Time (DST). Handling such anomalies requires you to specify a time zone as each zone has its own rules for its own anomalies. So apply a ZoneId to get a ZonedDateTime object.

ZoneId z = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ld.atStartOfDay( z );

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 2

All you can do is change to local date time with time set to start of the day since java sql date doesn't have time component. Use timezone or default to system time zone to get zoned date time

ZonedDateTime zonedDateTime = ZonedDateTime.of(result.get(0).toLocalDate().atStartOfDay(), ZoneId.systemDefault());