ItemWriter, SimpleJdbcTemplate & timezone pain

[matthewadams]

Hi all,

I'm running into a problem that I believe has more to do with Spring JDBC than Spring Batch, but I'm asking here because it's happening in the context of a step.

The problem I'm having is that a datetime field in my object needs to be written to the database in the UTC timezone, and it's being written in local time instead. I'm using the latest jTDS driver against Microsoft SQL Server 2008. Note that I'm also leveraging Joda for my datetime operations.

I wrote an ItemWriter that uses an injected SimpleJdbcTemplate. Here's the write(..) method:

	public void write(List<? extends List<IncomingBar>> listOfListOfBar)
			throws Exception {
		if (logger.isInfoEnabled()) {
			logger.info("inserting [" + listOfListOfBar.size()
					+ "] list(s) of bars");
			int i = 0;
			if (logger.isDebugEnabled()) {
				for (List<IncomingBar> bars : listOfListOfBar) {
					logger.debug("list [" + i++ + "] has [" + bars.size()
							+ "] bars");
				}
			}
		}

		jdbcTemplate.batchUpdate(
				getSql(),
				createSqlParameterSources(listOfListOfBar).toArray(
						new SqlParameterSource[0]));
	}

The createSqlParameterSources(..) method is:

	private List<SqlParameterSource> createSqlParameterSources(
			List<? extends List<IncomingBar>> listOfListOfBar) {

		List<SqlParameterSource> sqlParameterSources = new ArrayList<SqlParameterSource>(
				listOfListOfBar.size());

		for (List<IncomingBar> listOfBar : listOfListOfBar) {
			for (IncomingBar bar : listOfBar) {
				sqlParameterSources.add(sqlParameterSourceProvider
						.createSqlParameterSource(bar));
			}
		}
		return sqlParameterSources;
	}

The instance variable sqlParameterSourceProvider above is of the type IncomingBarSqlParameterSourceProvider. Here it is:

public class IncomingBarSqlParameterSourceProvider implements
		ItemSqlParameterSourceProvider<IncomingBar> {

	public SqlParameterSource createSqlParameterSource(IncomingBar bar) {
		Timestamp ts = new Timestamp(bar.getDateTime().getMillis());

		return new MapSqlParameterSource()
				.addValue("instrumentId", bar.getInstrumentId())
				.addValue("start", ts, Types.TIMESTAMP) //!!
				.addValue("open", bar.getOpen())
				.addValue("high", bar.getHigh()).addValue("low", bar.getLow())
				.addValue("close", bar.getClose())
				.addValue("volume", bar.getVolume());
	}
}

The offending field is marked with "//!!" above. Even though I'm specifying the sql type as TIMESTAMP and using the source time's milliseconds, which are the same regardless of the timezone, the database value ends up reflecting the value of that point in time in my JVM's local timezone instead of UTC.

Anyone know how to get SimpleJdbcTemplate to store datetimes in a specific timezone like UTC?

Thanks,
Matthew

[Dave Syer]

I somehow doubt this has anything to do with Spring JDBC either. You didn't really show the origin of the timestamp long data, so there's no way to know reading the code what the expected result is. Also how did you make the assertion about what is stored in the database? That's an additional possible source of a bug, plus I guess we can add the JDBC driver, for a total of 3. You need to narrow it down a bit.