将雪花 JDBC TIME 数据类型值转换为 UTC

Question

我们使用JDBC驱动程序连接到Snowflake并执行插入。 在使用 TIME 数据类型时，我们将时间值提供为 10：10：10，并在插入 setTime 时提供时间值，当使用 getTime 检索时，我们得到 02：10：10。

文档说 - TIME 内部存储"挂钟"时间，并且对 TIME 值的所有操作都是在不考虑任何时区的情况下执行的。

为什么要进行这种转换？如何确保我们取回插入的内容？

示例程序 -

try (Connection con = getConnection()) {
try (PreparedStatement ps = con.prepareStatement("insert into Test_Time values (?)"))  {
ps.setTime('10:10:10');
ps.executeUpdate();
}
try (Statement statement = con.createStatement()) {
try (ResultSet resultSet = statement.executeQuery("select * from Test_Time ")) {
while (resultSet.next()) {
System.out.println(resultSet.getTime(1));
}
}
}
}

我们都试过了——

ps.setTime(Time.valueOf('10:10:10'))
ps.setTime('10:10:10')

当从 WEB 门户工作表尝试类似时，我们看到相同的插入值 10：10：10。门户网站能够显示正确的预期结果，所以想知道我的情况是否缺少任何基本内容？

提前感谢您的帮助。

更新- 围绕这一点做了更多的测试 - 发现"setTime(int idx， Time t("和"setTime(int idx， Time t， Calendar c("之间没有区别。如果您已经知道数据的时区，则无法提供自定义时区。

Answer 1

转换由 Java 完成。如果使用绑定字符串变量而不是时间变量，您将看到它按预期工作：

CREATE TABLE test_time ( t TIME );

当我们运行下面的 Java 块时，它会在表中插入 "10：10：10"：

try (Connection con = getConnection()) {
try (PreparedStatement ps = con.prepareStatement("insert into Test_Time values (?)"))  {
ps.setString(1, "10:10:10" );
ps.executeUpdate();
}
try (Statement statement = con.createStatement()) {
try (ResultSet resultSet = statement.executeQuery("select * from Test_Time ")) {
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
System.out.println(resultSet.getTime(1));
}
}
}
}

您将看到以下输出：

10:10:10
02:10:10

第一个是将时间读取为字符串，第二个是使用 Time 对象，以便将其转换为您的时区。如果在 Web UI 上查询表，您将看到它将返回预期值：

SELECT * FROM test_time;
+----------+
|    T     |
+----------+
| 10:10:10 |
+----------+

至少，它对我有用:)

Answer 2

从我所看到的搜索(例如这里(来看，它似乎归结为您正在使用的驱动程序而不是数据库。我也看到了不同的结果 - 在我的脚本中，我插入了10:10:10并返回10:10:10但是当我查看雪花表时，它说09:10:10.这是因为我住在伦敦，目前是 BST (GMT+1(，所以我的驱动程序在插入 Snowflake 之前(运行ps.setTime时(以及从数据库检索时(运行results.getTime时(正在从我的本地时区转换为 GMT。

在scala中的示例，但基本相同的代码：

val ps: PreparedStatement = connection.get.prepareStatement("insert overwrite into test_db.public.test_table values (?)")
val insertedTime: Time = Time.valueOf("10:10:10")
println(s"Inserting ${insertedTime} into table")
ps.setTime(1, insertedTime)
ps.executeUpdate()
val results: ResultSet = connection.get.createStatement().executeQuery("select time_col from test_db.public.test_table")
while(results.next) {
val returnedTime = results.getTime(1)
println(s"Time returned: ${returnedTime}")
}

上面的脚本打印出来：

Inserting 10:10:10 into table
Time returned: 10:10:10

在雪花中，时间是：

+----------+
| time_col |
+----------+
| 09:10:10 |
+----------+

要解决此问题，您可以将全局时区设置为 GMT 并运行相同的脚本。下面是上面的相同示例，时区更改为 GMT：

TimeZone.setDefault(TimeZone.getTimeZone("GMT+00:00")) // <----- ** New bit **
val ps: PreparedStatement = connection.get.prepareStatement("insert overwrite into test_db.public.test_table values (?)")
val insertedTime: Time = Time.valueOf("10:10:10")
println(s"Inserting ${insertedTime} into table")
ps.setTime(1, insertedTime)
ps.executeUpdate()
val results: ResultSet = connection.get.createStatement().executeQuery("select time_col from test_db.public.test_table")
while(results.next) {
val returnedTime = results.getTime(1)
println(s"Time returned: ${returnedTime}")
}

现在脚本打印(相同(：

Inserting 10:10:10 into table
Time returned: 10:10:10

在《雪花》中，时间现在显示了你所期望的：

+----------+
| time_col |
+----------+
| 10:10:10 |
+----------+