1

I'm using Java 8 + Spring 5.1.5R + mybatis 3.5.6 with PostgreSQL 11.

I executed select query with date, timestamp, timestamptz columns like this.

SELECT t_date ,t_timestamp ,t_timestamptz FROM a_table

And I can see ibatis log like this.

t_timestamptz column has it's offset and t_timestamp column has no offset.

DEBUG org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(137) - ==>  Preparing: SELECT t_date ,t_timestamp ,t_timestamptz FROM a_table LIMIT ?
DEBUG org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(137) - ==> Parameters: 1(Int)
TRACE org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(143) - <==    Columns: t_date ,t_timestamp ,t_timestamptz
TRACE org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(143) - <==        Row: 2020-11-05, 2020-11-05 22:27:44, 2020-11-05 22:27:56.402235+09
DEBUG org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(137) - <==      Total: 1

But, I can see only java.sql.Timestamp and java.sql.Date.

t_date: java.sql.Date:  2020-11-05
t_timestamptz:  java.sql.Timestamp: 2020-11-05 22:27:56.402235
t_timestamp:    java.sql.Timestamp: 2020-11-05 22:27:44.0

How can I get timestamptz column's value with offset and timestamp column's value without offset?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
jaehak Lee
  • 63
  • 1
  • 1
  • 6
  • 1
    A `java.sql.Timestamp` has no knowledge of time zones. You would need to retrieve as an `java.time.OffsetDateTime`, but as far as I'm aware, the PostgreSQL JDBC driver doesn't preserve offset in a useful manner. – Mark Rotteveel Nov 07 '20 at 09:13
  • Like most DB engines PostgreSQL stores *timestamp with time zone* as *timestamp in UTC*. From [the docs](https://www.postgresql.org/docs/9.1/datatype-datetime.html): *For `timestamp with time zone`, the internally stored value is always in UTC … An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the `timezone` zone.* – Ole V.V. Nov 07 '20 at 09:39

0 Answers0