import java.sql.Timestamp; //导入方法依赖的package包/类
/**
* Tests fix for BUG#5874, timezone correction goes in wrong 'direction' (when useTimezone=true and server timezone differs from client timezone).
*
* @throws Exception
* if the test fails.
*/
public void testBug5874() throws Exception {
TimeZone defaultTimezone = TimeZone.getDefault();
try {
String clientTimezoneName = "America/Los_Angeles";
String serverTimezoneName = "America/Chicago";
TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));
long clientTimezoneOffsetMillis = TimeZone.getDefault().getRawOffset();
long serverTimezoneOffsetMillis = TimeZone.getTimeZone(serverTimezoneName).getRawOffset();
long offsetDifference = clientTimezoneOffsetMillis - serverTimezoneOffsetMillis;
SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
long pointInTime = timestampFormat.parse("2004-10-04 09:19:00").getTime();
Properties props = new Properties();
props.put("useTimezone", "true");
props.put("serverTimezone", serverTimezoneName);
props.put("cacheDefaultTimezone", "false");
Connection tzConn = getConnectionWithProps(props);
Statement tzStmt = tzConn.createStatement();
createTable("testBug5874", "(tstamp DATETIME, t TIME)");
PreparedStatement tsPstmt = tzConn.prepareStatement("INSERT INTO testBug5874 VALUES (?, ?)");
tsPstmt.setTimestamp(1, new Timestamp(pointInTime));
tsPstmt.setTime(2, new Time(pointInTime));
tsPstmt.executeUpdate();
this.rs = tzStmt.executeQuery("SELECT * from testBug5874");
while (this.rs.next()) { // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types when calling getString()...
String retrTimestampString = new String(this.rs.getBytes(1));
Timestamp retrTimestamp = this.rs.getTimestamp(1);
java.util.Date timestampOnServer = timestampFormat.parse(retrTimestampString);
long retrievedOffsetForTimestamp = retrTimestamp.getTime() - timestampOnServer.getTime();
assertEquals("Original timestamp and timestamp retrieved using client timezone are not the same", offsetDifference,
retrievedOffsetForTimestamp);
String retrTimeString = new String(this.rs.getBytes(2));
Time retrTime = this.rs.getTime(2);
java.util.Date timeOnServerAsDate = timeFormat.parse(retrTimeString);
Time timeOnServer = new Time(timeOnServerAsDate.getTime());
long retrievedOffsetForTime = retrTime.getTime() - timeOnServer.getTime();
assertEquals("Original time and time retrieved using client timezone are not the same", offsetDifference, retrievedOffsetForTime);
}
tzConn.close();
} finally {
TimeZone.setDefault(defaultTimezone);
}
}