how to convert from LocalDateTime (java) to datetime (sql)?

Question

how to convert from LocalDateTime (java) to datetime (sql) ?

Answer 1

The 'SQL native' time types in the java.sql packages are kinda bad - there is nothing in there that represents 'a date and a time without any timezone information'.

Databases also have wildly different ways of storing such information. The appropriate way to store it, is to store the year, month, day, hour, minute, second, and potentially, milli/nanos all as separate entities (and to forego entirely the notion of trying to go via millis-since-some-epoch-someplace).

Fortunately, JDBC does offer infrastructure for database drivers to use custom datatypes.

For example, the PSQL driver lets you write this:

somePreparedStatementObject.setObject(1, yourLocalDateTimeObject);

This works.. IF you're using it in a place that is appropariate (for example, as value in an INSERT statement for a column whose type is TIMESTAMP WITHOUT TIMEZONE).

Getting such a field out is also possible, directly:

someResultSet.getObject(1, LocalDateTime.class);

This gets you a LocalDateTime, directly.

Thus sidestepping hairy timezone issues, which occur if you convert, which all the java.sql time/date types do!

So, try the above first. If your DB engine is kinda cruddy and won't let you do the above, okay, then convert, presumably via UTC, but don't be surprised if your dates are all of a sudden off by a day if your DB server and the JVM are running on machines with different configs for 'system default timezone', or for weirdness to ensue when it's winter and you're setting/querying a date in summer.