1

I use Redshift and jdbc. The timezone of redshift server show timezone; is UTC. My application(jvm) timezone is +2.

When I execute the query via jdbc select SYSDATE; I got the correct Timestamp with correct value in UTC, but unfortunately the time zone of this value is +2.

For example: Server has 2019-06-26T18:10:13.000+0-00 but I got 2019-06-26T18:10:13.000+0200.

After that I mapped via jackson the timestamp to json and send response to client, and client got incorrect value 2019-06-26T16:10:13.000+0000.

I can't change the timezone for application and for mapper.

How I can resolve this problem?

Иван Гладуш
  • 612
  • 12
  • 27

2 Answers2

1

You need to specify that the date is UTC, by specifying a TimeZone using the getTimestamp(String columnLabel, Calendar cal) method:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

Timestamp ts = resultset.getTimestamp("TimestampColumn", cal);

As the javadoc says:

This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.

Andreas
  • 154,647
  • 11
  • 152
  • 247
1

I seems that the JDBC driver does not have a parameter to set client's timezone. This is a similar question for postgres (and redshift and postgres will most probably share features/problems).

Option 1 - change client JVM timezone

So if it's an option for you, looks like you can change the timezone setting in your JVM as JDBC client is using client's timezone in the connection. 

AddVMOption -Duser.timezone=UTC

Option 2 - send SET SESSION TIME ZONE before query

I know that in psql you can control client's timezone by either setting PGTZ environment variable (I used it to connect to Redshift and was getting TIMESTAMP WITHOUT TIME ZONE in desired form. This postgres documentation says:

  • The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

Maybe that would be an option to always send SET TIME ZONE UTC (see Redshift timezone config docs) upon opening your JDBC session, or before each query.

botchniaque
  • 4,698
  • 3
  • 35
  • 63