SQL根据时区查询日期

Question

我们使用的是 Vertica 数据库，其表列类型为 timestamptz，所有数据均根据 UTC 时区插入。我们使用 spring-jdbc 的 NamedParameterJdbcTemplate

所有查询均基于完整日历日，例如开始日期 2013/08/01 和结束日期 2013/08/31，这带来了“2013/08/01 00:00:00.0000”和“2013/08/31 23:59:59.9999”之间的所有内容

我们正在尝试修改我们的查询以考虑时区，即我可以对于我的本地时区，我可以要求“2013/08/01 00:00:00.0000 Asia/Jerusalem”直到“2013/08/31 23:59” :59.9999 Asia/Jerusalem'，这与 '2013/08/01 00:00:00.0000 UTC' 到 '2013/08/31 23:59:59.9999 UTC' 明显不同。

到目前为止，我找不到办法这样做，我尝试在 session 中设置时区:

将时区设置为“亚洲/耶路撒冷”；

这甚至在我的数据库客户端中都不起作用。

计算 Java 代码中的差异对我们来说不起作用，因为我们还有返回日期分组的查询(这将完全困惑)。

有什么想法或建议吗？

Answer 1

我不熟悉 Veritca，但有一些一般性建议:

• 通常最好使用半开间隔进行日期范围查询。开始日期应包含在内，而结束日期应不包含。换句话说:

  start <= date < end
  

  或

  start <= date && end > date
  

  您的结束日期不会是'2013/08/31 23:59:59.9999'，而是第二天的开始，或'2013/09/01 00:00:00.0000'。这避免了与小数精度相关的问题。

  该示例用于查找单个日期。由于您正在查询一系列日期，因此您有两个输入。所以它会是:

  startFieldInDatabase >= yourStartParameter
      AND
  endFieldInDatabase < yourEndParameter
  

  同样，您首先需要将结束参数值增加到第二天的开始时间。

• 鉴于您在答案中谈到了 timestamptz 类型，听起来 Vertica 可能支持 TZ。假设它们类似于 Oracle's TIMESTAMPTZ type ，那么听起来您的解决方案就可以正常工作。

• 但通常，如果您在数据库中存储 UTC 时间，那么您只需提前转换查询输入时间即可。因此，您不需要在 '2013/08/01 00:00:00.0000' 和 '2013/09/01 00:00:00.0000' 之间查询，而是提前转换它'2013/07/31 21:00:00.0000' 和 '2013/08/31 21:00:00.0000' 之间的时间和查询。已经有很多帖子介绍了如何在 Java 中以 native 方式或使用 Joda Time 进行该转换。 ，所以这里不再重复。

• 顺便说一句，您应该确保您使用的任何 TZDB 实现(Vertica、Java 或 JodaTime)都具有最新的 2013d update ，因为其中包括 change for Israel's daylight saving time rule今年生效。