Save entity with java.time.Instant property using NamedParameterJdbcTemplate with postgres driver

Question

I am having issues saving my entity that has a Instant value using the NamedJdbcTemplate. I receive the exception listed below and I am trying to determine the best way to resolve this. Please advise.

Can't infer the SQL type to use for an instance of java.time.Instant. Use setObject() with an explicit Types value to specify the type to use.

I am using the postgresql:9.4.1212 driver

Aeroballistics

I am able to save entities that have LocalDate and LocalDateTime properties but not Instant. Can someone explain why this is still not supported in the JDBC driver? – Aeroballistics

Why are you using such an outdated driver? Did you try with a more recent version? – Cornflakes

This is the driver our prod environment uses and I must use this version. I tried the latest of this driver and it still has the issue. All other Types in the java.time.* api work without issue but for some reason the driver doesn't support instant – Aeroballistics

What is the data type of the column? I can't think of any Postgres type that would map to a java.tiime.Instant. Why not use LocalDate or LocalDateTime? – Cornflakes

I have tried a timestamp column with and without timezone. Should probably actually try a big int. Don't want to use those types as they carry inherent knowledge of timezones and I would like to avoid anything relating to timezones and let each app use the timezone it needs as the Instant will be represented as UTC. I guess I could create localDateTime's and convert but would like to avoid that as it gets tricky in some edge cases – Aeroballistics

Then use a LocalDateTime that does not carry any timezone information. Passing that through setObject() works for me - at least with the most recent driver version, not sure if that old version supports that. – Cornflakes

localdatetime inherently doesn't carry the timezone info. it just creates a date in the system default timezone which is going to be an inaccurate time when read in other TZ. – Aeroballistics

The convert the LocalDateTime to a value in UTC before saving – Cornflakes

Answer 1

This code worked for me:

Instant ts = // whatever instant
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("ts", Timestamp.from(ts), Types.TIMESTAMP);

jdbcTemplate.queryForList(SQL, params)

where SQL is the parametrized query and make sure you're using java.sql.Timestamp and java.sql.Types.

Claudclauddetta