Converting Joda LocalTime to java.sql.Date

Question

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?

Answer 1

It should be safe to use your technique because all the timezone issues will be taken into account by LocalDate#toDate. The resulting millisecond instant you have is context-independent: it uniquely relates to a timezone valid at that point in time within the locale you are using for conversion. In other words, if you repeat the conversion of the exact same millisecond value throughout a year, you will consistently get the exact same answer, even if timezone regulations change for your place in the meantime, since JDK refers to a database documenting the complete history of all timezone changes around the world.

When reasoning about these issues it is important to remember that your current timezone has no effect on the conversion, which is parameterized by your locale and resolves the timezone only within the context of the instant being converted.

I wholeheartedly sympathize with the queasiness you fell about all this: it is turning a simple and straigtforward operation into a complex maze of calculations which does nothing but invite trouble. Hopefully things will take a positive turn with Java 8 and its new (yes, again!) Date/Time API, based firmly on JodaTime.

Answer 2

I got the same problem today. I'm using JDK 8. After spending some hours searching finally I found the answer at Java SE 8 Documentation. This is the solution :

statement.setDate(5, java.sql.Date.valueOf(personToUpdate.getBirthday()));

statement is PreparedStatement instance. "personToUpdate.getBirthday()" is type of LocalDate.

Answer 3

Since org.joda.time.toDateMidnight() and org.joda.time.toDateMidnight(DateTimeZone zone) have been deprecated, this is the solution that works perfectly for me.

My typical Class, to be persisted:

    ...
    import org.joda.time.LocalDate;
    ...

    public class MyObject implements Serializable {

      ...
      private LocalDate startDate;

      ...
      private EndDate startDate;


      // Getters and Setters
      ...

      ...
    }

Im my other Class where I persist startDate, I have:

    myObject.setStartDate(new LocalDate(myObject.getStartDate().toDateTimeAtStartOfDay(DateTimeZone.getDefault())));

Answer 4

tl;dr

myPreparedStatement.setObject(   // Pass java.time objects directly to database with JDBC 4.2 or later.
    … , 
    LocalDate.now()              // Get current date today. Better to pass optional `ZoneId` time zone object explicitly than rely implicitly on JVM’s current default.
)

java.time

In Java 8 and later, the new java.time framework is now built-in. This successor to Joda-Time is defined by JSR 310 and extended by the ThreeTen-Extra project.

Hopefully we well eventually see the JDBC drivers updated to directly handle the new java.time types. But until then we continue to need the java.sql.* types. Fortunately, new methods have been added to conveniently convert between the types.

For a date-only, with no time-of-day and no time zone, the Java type is LocalDate (quite similar to Joda-Time).

As for your concern about time zone related to a LocalDate, it matters when your are translating a date-only to a date-time, to a moment on the timeline. A date-only is just a vague idea, with no real meaning, until you translate it to a time-span of moment on the timeline (midnight to midnight in some time zone). For example, determining "today" requires a time zone. In java.time we use the ZoneId class.

LocalDate today = LocalDate.now( ZoneId.of( "America/Montreal" ) );

If omitted, your JVM’s current default time zone is used in determining the date. In other words, the following two lines are equivalent.

LocalDate today = LocalDate.now();
LocalDate today = LocalDate.now( ZoneId.systemDefault() );

I consider the first version, now(), to be a poor API design choice. This implicit application of the JVM’s current default time zone causes no end of confusion, bugs, and misery among naïve developers. For one thing, the JVM's current default varies by machine, by host OS settings, and by sysadmins. Worse, the JVM’s current default can change at any moment, during runtime, by any code in any thread of any app within that JVM. So best practice is to always specify your desired/expected time zone.

Now that we have a java.time object for "today", how to get it into the database?

With JDBC 4.2 or later, directly exchange java.time objects with your database.

myPreparedStatement.setObject( … , today ) ;

To retrieve:

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

If you cannot upgrade yet to JDBC 4.2 or later: Use a java.sql.Date object. In Java 8, that old class gained new methods, toLocalDate and valueOf. The latter is our bridge from the java.time type to the java.sql type.

java.sql.Date sqlToday = java.sql.Date.valueOf( today );

From there do the usual PreparedStatement handling.

myPreparedStatement.setDate( 1 , sqlToday );

Date-only vs Date-time

Perhaps you have concerns about such a date-only fitting your business needs.

If you need to know, for example, if a contract was signed by the end of the day for legal reasons, then date-only is the wrong data-type if mean a specific moment such as the stroke of midnight in Montréal. A new day dawns earlier in Paris than in Montréal, so "today" in Paris is "yesterday" in Montréal. If your contract deadline is defined legally as the end of the day in Montréal, then you must apply a time zone. To apply a time zone, you must have a date-time rather than a date-only. You can make a jump from the LocalDate into a ZonedDateTime, but I consider that overly complex. Your database should have used a date-time type from the beginning.

In Postgres, a date-time type means the TIMESTAMP WITH TIME ZONE type. That name is a misnomer as the time zone is not actually stored. Think of it as “timestamp with respect for time zone”. Postgres uses any offset-from-UTC or time zone information accompanying incoming data to adjust to UTC, and that offset/zone info is then discarded. The other type, TIMESTAMP WITHOUT TIME ZONE, ignores the offset/zone info entirely, and this is the wrong behavior for most any business app.

I suspect many developers or DBAs may make the naïve mistake of thinking by intuition that the date-only has obvious meaning. But in fact if you have specific or strict moment-oriented needs, such as legalities regarding events such as “contract signed”, “invoice received”, or “company executive hired”, then a date-time value should be used rather than date-only.

In other words, regarding the Question’s author’s comment:

And I expected there should be a way to work with dates without resorting to time instances.

No, I would argue that is asking for trouble. If moments matter, use a date-time rather than a date-only.

---

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.

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, 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 5

Try to Use LocalDate#toDateMidnight() which sets time to 0 and then DateMidnight#toDate().

Date date = localDate.toDateMidnight().toDate();

Or if you're using JodaTime 1.5 or newer, use LocalDate#toDateTimeAtStartOfDay() and then DateTime#toDate()

Hope that help