pgjdbc: we no longer ever return TIMESTAMP_WITH_TIMEZONE

https://github.com/pgjdbc/pgjdbc/blob/f2d8ec5740aa26c417d666a7afedaaf0fdf62d37/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java#L3384

          Oid.TIMESTAMPTZ_ARRAY},

Does anyone remember why we changed this ?

About this issue

Original URL
State: open
Created 7 years ago
Comments: 29 (18 by maintainers)

Most upvoted comments

@davecramer I encounter a problem that I cannot use Spring’s SimpleJdbcInsert since the column type of a “timestamptz” is determined incorrectly as Types.TIMESTAMP instead of Types. TIMESTAMP_WITH_TIMEZONE. My OffsetDateTime is then processed incorrectly in PgPreparedStatement.setObject.

Is it possible to return the correct SQL type?

Stacktrace:

Caused by: org.postgresql.util.PSQLException: Bad value for type timestamp/date/time: {1}
	at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:349)
	at org.postgresql.jdbc.TimestampUtils.toTimestamp(TimestampUtils.java:380)
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:631)
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:907)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:437)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:238)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:169)
	at org.springframework.jdbc.core.simple.AbstractJdbcInsert.setParameterValues(AbstractJdbcInsert.java:604)
	at org.springframework.jdbc.core.simple.AbstractJdbcInsert.access$100(AbstractJdbcInsert.java:62)
	at org.springframework.jdbc.core.simple.AbstractJdbcInsert$3.setValues(AbstractJdbcInsert.java:580)
	at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:960)
	at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
	... 47 more
Caused by: java.lang.NumberFormatException: Trailing junk on timestamp: 'T15:51:56.841+01:00'
	at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:339)
	... 60 more

paulmiddelkoop on Nov 28, 2017

@vlsi I have a POJO containing an OffsetDateTime which I try to insert with SimpleJdbcInsert, so I got the OffsetDateTime myself.

SimpleJdbcInsert is called simple because it’s simple and does not support functions so I think using types is not broken.

I agree that changing the returned data type will indeed break down Hibernate except when they are willing to change the Postgres Dialect. See this thread without an conclusion: https://forum.hibernate.org/viewtopic.php?f=1&t=1043802&p=2490936. Spring also had an issue for that but it seems like they forgotten to add TIMESTAMP_WITH_TIMEZONE, see https://jira.spring.io/browse/SPR-11600.

So I agree with you that if you change to TIMESTAMP_WITH_TIMEZONE it should be under a feature flag and Hibernate and Spring and probably others should also be adjusted. I think this is the best way in the end, but also difficult. I will also be happy with an adjustment that setObject with a TIMEZONE is going to support an OffSetDateTime input.

@davecramer Let me know what you guys decide. If you proceed with the TIMESTAMP_WITH_TIMEZONE change, I will test the PR you created.

paulmiddelkoop on Jan 10, 2018

@vlsi I think technically the bug is that pgjdbc returns an incorrect data type. Frameworks like Spring and JOOQ will then call a setObject with an incorrect data type. You could indeed also support OffsetDateTime in the setObject for a Timestamp. That sounds like a work-around instead of a real solution since the problem still exists. Other code that depends on a correct data type determination will also not work correctly.

paulmiddelkoop on Jan 10, 2018