I am using a callable statement of type SQLServerCallableStatement because I need to call a stored procedure which accepts a table type argument. The prepared statement has a method setDateTime() which I use to set the start and end date of the queries like so:
DateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
formatter.setTimeZone(TimeZone.getTimeZone("GMT"));
Timestamp start = new Timestamp(formatter.parse(startDate).getTime());
Timestamp end = new Timestamp(formatter.parse(endDate).getTime());
Timestamp asOf = (asOfDate == null || asOfDate.isEmpty()) ? null : new Timestamp(formatter.parse(asOfDate).getTime());
final String sprocSQL = "EXEC timeseries.selectMultipleTimeseries @identifier_list=?, @dataset=?, @field_list=?, @publisher=?, @start_date=?, @end_date=?, @asof_date=?";
SQLServerCallableStatement stmt = connection.prepareCall(sprocSQL).unwrap(com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.class);
stmt.setStructured(1, "timeseries.IdentifierList", identifierList);
stmt.setString(2, dataset);
stmt.setStructured(3, "timeseries.FieldList", fieldList);
stmt.setString(4, publisher);
stmt.setDateTime(5, start);
stmt.setDateTime(6, end);
stmt.setDateTime(7, asOf);
startDate and endDate are in GMT and are correct. start and end are in localtime and are also correct (I am Europe/London DST which is GMT+1). The problem seems to be that what is passed to the database is localtime but the database interprets it as GMT. So if startDate is 09:00am I get data from 10:00am. What I suspect is happening is that somehow the Timestamp variables start and end are taken as strings and this is passed to the database without TZ info. Is there a way to add this timezone information to setDateTime().
Looking at start, the epoch is correct, 9am. However, when I print it as string it shows as 10am which is what the query returns - data from 10am.
