How do I specify the timezone when inserting or updating a row with timestamp in Spring JDBC Template?

3
votes

In JDBC API, I can simply set UTC timezone using the following

For inserting or updating

PreparedStatement myPreparedStatement = ...
myPreparedStatement.setTimestamp( 1, myDateTime, Calendar.getInstance( TimeZone.getTimeZone( "UTC" ) ));

For querying

ResultSet rs = ...
rs.next();
rs.getTimestamp( 1,  Calendar.getInstance( TimeZone.getTimeZone( "UTC" ) ) )

Using Spring JDBC Template, I will need to use something like this to query.

For querying, I don't have any issues because ResultSet is accessible via RowMapper and use the ResultSet to pass on the Calendar instance in UTC. However, for inserting or updating, I only have the following.

NamedParameterJdbcTemplate namedParameterJdbcTemplate = ....
SqlParameterSource parameters = new MapSqlParameterSource().addValue("current_timestamp", myDateTime )
namedParameterJdbcTemplate.update(sql, parameters);

Question

Is there a way to tell the Spring JDBC template to use my specified TimeZone?

I do not prefer to use the TimeZone.setDefault(..) because that's going to affect our legacy modules.

2
spring-jdbcjdbctemplate

2 Answers

1
votes

You can use any method of JdbcTemplate which takes PreparedStatementCreator or PreparedStatementSetter as a parameter and set the field's meta information on those objects.

-1
votes

You do not use time zones when setting or storing a date object. Nor should you. Which is why Date objects have no time zone. A date is just a point on a time line.

The time zone offset is set by the client or consuming application so it can use the correct offset for its location/requirements. If you create an offset in the date value everything that consumes that data down stream will always have to know that date is off and will have to manually account for it. Which grows over time to be a huge pain.