2

My application uses Database-per-tenant multitenancy, and each tenant has a timezone.

I don't want to use Timestamp as I don't want the database handling the timezone translation, but what I would like to do is convert between ZonedDateTime and MySQL DateTime.

In my application, I retrieve the current time for the tenant like so :

ZonedDateTime.of(LocalDateTime.now(), tenant.getTenantZoneId());

I want to write this to the database as the zoned date time would show it (i.e. with the timezone and DST applied).

Upon reading from the database, this transformation shouldn't be necessary, and I can just assume the timezone for all datetimes.

How can I do that initial translation? And how can I do this in a nice way a JPA provider (especially hibernate)

Jordan Mackie
  • 2,264
  • 4
  • 25
  • 45

1 Answers1

3

If I understand you right, you want to write ZonedDateTime to MySQL's DATETIME type, but without preserving zone ID, etc. If so, you simply want to write it as LocalDateTime.

If your driver is recent enough, simply writing LocalDateTime to the database using PreparedStatement.setObject should work:

preparedStatement.setObject(index, zonedDateTime.toLocalDateTime());

As for reading, ResultSet.getObject should work:

LocalDateTime localDateTime = resultSet.getObject(index, LocalDateTime.class);
ZonedDateTime zonedDateTime = ZonedDateTime.of(localDateTime, tenant.getTenantZoneId());
Tomasz Linkowski
  • 4,386
  • 23
  • 38
  • 1
    This is really good! I should've mentioned in the question that I'm using Hibernate JPA, but I think it can handle the localdatetime type. I'll check, update my question then accept if it can! – Jordan Mackie Aug 06 '18 at 17:14
  • 1
    Going to link this answer [here](https://stackoverflow.com/a/32680455/4364101) as it explains localdatetime works with hibernate 5. – Jordan Mackie Aug 06 '18 at 17:51