CODEWITHSUNDEEP
javaspringjdbc
greyfox
greyfox

Reputation: 6606

Spring JDBC Persist ZonedDateTime

I am working with clock in / out functionality from a web portal that can be accessed by terminals in different timezones.

I am using ZonedDateTime to handle calculating what the datetime should be for a given punch in / out record.

// Get store timezone
ZoneId storeTimeZone = this.storeService.getStoreZoneId(storeNum);

// Get current store time to use for punch in / out timestamp
ZonedDateTime currentStoreTime = ZonedDateTime.ofInstant(Instant.now(), storeTimeZone);

When I go to persist the data to Oracle 11g I can't persist a ZonedDateTime object. Likely not supported by the JDBC driver. Its a corporate environment so database and driver are not going to change.

When I am building the MapSqlParamSource to pass into NamedParameterJdbcTemplate I am having to convert it to a java.sql.Timestamp but then I'm losing the Zone data and storing the timestamp for the timezone the server is in, requirements dictate it has to be stored in store time in database which is just an Oracle TIMESTAMP column.

params.addValue("clockInTimestamp", Timestamp.from(prevRecord.getTimeEntryTimestamp().toInstant()), Types.TIMESTAMP);

Is there a way to accomplish this persisting ZonedDateTime?

Upvotes: 2

Views: 7360

Answers (3)

Flatback
Flatback

Reputation: 31

I managed to fix this for MySQL so it might work for others. I added sqlType parameter when adding it to MapSqlParameterSource

ZonedDateTime zdt = ZonedDateTime.now();
LocalDateTime ldt = LocalDateTime.ofInstant(zdt.toInstant(), ZoneId.systemDefault());

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("datetime", ldt, Types.JAVA_OBJECT);

Upvotes: 3

greyfox
greyfox

Reputation: 6606

I was able to solve this issue by converting from ZonedDateTime to Instant and then manually calculating the timestamp using the timezone offsets.

Example code below:

ZonedDateTime timeEntry = ZonedDateTime.now();

ZoneOffset storeOffset = timeEntry.getOffset();
ZoneOffset serverOffset = ZonedDateTime.now().getOffset();

int secondsOffset = storeOffset.getTotalSeconds() - serverOffset.getTotalSeconds();

Instant entryTimestamp = timeEntry.toInstant().plusSeconds(secondsOffset);

Timestamp ts = Timestamp.from(entryTimestamp);

And then I persist the java.sql.Timestamp to the database.

Not the cleanest or best solution I'm sure, but with the constraints of not being able to change database, drivers, or way other things were working it seems to do well.

Upvotes: 0

dillius
dillius

Reputation: 516

Sprint Boot projects will automatically load jsr310jpaconverters, but this will only handle conversion of LocalDateTime type, not ZonedDateTime. If you're not using Spring Boot you can load these extra converters yourself.

You may want to adapt your application to make use of those at the database interaction level and just force everything you save to be in one standard time zone so you can easily convert to ZonedDateTime if required for user display purposes.

Upvotes: 1

Related Questions