在jTDS中使用datetimeoffset数据类型 [英] using the datetimeoffset datatype with jTDS
问题描述
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?
推荐答案
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数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!