问题描述
jTDS当前不支持SQL Server 2008中引入的datetimeoffset数据类型.
jTDS currently doesn't support the datetimeoffset datatype introduced in SQL Server 2008.
有人可以建议jTDS是否可以使用datetimeoffset类型吗?
Can anybody suggest if there is a way to use the datetimeoffset type with jTDS?
推荐答案
如 datetimeoffset文档，我们可以使用datetimeoffset值的字符串表示形式.实际上，如果我们使用jTDS 1.3.1检索datetimeoffset值，则会得到形式为的java.lang.String
As mentioned in the "Backward Compatibility for Down-level Clients" section of the datetimeoffset documentation, we can work with string representations of datetimeoffset values. In fact, if we retrieve a datetimeoffset value with jTDS 1.3.1 we get a java.lang.String value of the form
YYYY-MM-DD hh:mm:ss[.nnnnnnn] {+|-}hh:mm
可以像这样解析这样的值:
Such a value can be parsed like so:
// rs is our ResultSet object
String valueRetrieved = rs.getString(1); // e.g., "2016-12-08 12:34:56.7850000 -07:00"
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSS ZZZZZ");
ZonedDateTime zdt = ZonedDateTime.parse(valueRetrieved, dtf);
对于将datetimeoffset值写入SQL Server，jTDS无法使用.setTimestamp正确处理更新，例如在我的机器上...
As for writing a datetimeoffset value to SQL Server, jTDS is unable to properly handle an update using .setTimestamp, e.g., on my machine ...
java.sql.Timestamp ts = java.sql.Timestamp.valueOf("2016-12-08 12:34:56.785"); // local
String tsString = formatTimestampForDateTimeOffset(ts); // (see below)
System.out.printf(" java.sql.TimeStamp value: %s (%d ms since epoch)%n", tsString, ts.getTime());
System.out.println();
System.out.println("Saving via setTimestamp ...");
String sqlUpdate = "UPDATE dtoTable SET dtoCol = ? WHERE id=1";
try (PreparedStatement s = conn.prepareStatement(sqlUpdate)) {
s.setTimestamp(1, ts); // pass the Timestamp itself
s.executeUpdate();
}
String valueRetrieved;
try (
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT dtoCol FROM dtoTable WHERE id=1")) {
rs.next();
valueRetrieved = rs.getString(1);
System.out.printf(" jTDS saved the TimeStamp value as: %s%n", valueRetrieved);
}
...产生...
java.sql.TimeStamp value: 2016-12-08 12:34:56.785 -07:00 (1481225696785 ms since epoch)
Saving via setTimestamp ...
jTDS saved the TimeStamp value as: 2016-12-08 12:34:56.7870000 +00:00
...不仅会错误地将时区偏移设置为+00:00(而不更改日期/时间值本身)，而且还会增加几毫秒的娱乐时间.
... which not only incorrectly sets the timezone offset to +00:00 (without changing the date/time value itself), it also adds a couple of milliseconds just for fun.
但是，如果我们将时间戳记值转换为格式正确的字符串，例如...
However, if we convert the Timestamp value to a properly-formatted String, e.g., ...
public static String formatTimestampForDateTimeOffset(java.sql.Timestamp ts) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS ZZZZZ");
String s = sdf.format(new Date(ts.getTime()));
// T-SQL *requires* the colon in the timezone offset: -07:00, not -0700
int colonPosition = s.length() - 2;
return s.substring(0, colonPosition) + ":" + s.substring(colonPosition);
}
...并使用.setString而不是.setTimestamp，则datetimeoffset值将正确保存:
... and use .setString instead of .setTimestamp, then the datetimeoffset value is saved correctly:
Saving via setString ...
jTDS saved the formatted String as: 2016-12-08 12:34:56.7850000 -07:00
parsed to ZonedDateTime: 2016-12-08T12:34:56.785-07:00
converted to Instant: 2016-12-08T19:34:56.785Z
converted to java.util.Date: Thu Dec 08 12:34:56 MST 2016
这篇关于在jTDS中使用datetimeoffset数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！