PostgreSQL - 以本地时间提取日期范围？

Question

当前情况:

• 我有一张野火事件表，其中包含一个timestamp with time zone (timestamptz) 字段，用于跟踪观察发生的时间。
• 数据创建过程中的一切都在 UTC 中:来自源的传入数据、插入数据的应用程序服务器、插入 python 代码(在时间后附加一个“Z”)和数据库服务器都在世界标准时间。
• 事件的地理范围跨越美国、加拿大和墨西哥的多个时区。

问题:

• 我一直在查询 UTC 时间一天的数据，但需要提取相对于本地时间的数据。每个时区的午夜至午夜范围会有所不同。
• 我现在的用例是一天，但我被要求考虑任意时间范围。例如:查找本地时间一天中 HitTest 时段(比如 10:00 到 18:00)发生的所有事件。
• 这个表非常大，我现在在 timestamptz 字段上有一个索引。我所做的任何更改都需要使用索引。
• 考虑夏令时。

我有一个方法可以获取每条记录的时区，所以我不需要需要帮助。

我为此创建了一个测试表，其中包含一个 timestamptz 字段 ts_with 和一个用于时区 tz 的 varchar 字段。下面的查询返回了我想要的，所以我觉得我正在取得进展。

SELECT 
    name, test_tz.ts_with, test_tz.tz, 
    TIMEZONE(test_tz.tz, test_tz.ts_with) as timezone_with
FROM fire_info.test_tz
WHERE TIMEZONE(test_tz.tz, test_tz.ts_with) BETWEEN 
    '2018-08-07 00:00:00' AND '2018-08-07 23:59:59';

问题:

• 这会使用我的索引吗？我认为时区功能会避免它。有什么解决办法吗？我正在考虑在 where 子句中添加另一个条件，该条件选择在两边都缓冲了一天的 timestamptz 上。那将使用索引，然后时区功能不会对太多数据进行排序(火灾季节每天约 6k 条记录)。 PG会明白吗？
• 时区功能为我提供了 DST 偏移量(例如:丹佛目前是 UTC-06)。我假设我会在 DST 结束后获得标准时间。如果我在 12 月运行查询以获取 8 月的数据，它会应用标准时间还是 DST？

谢谢!!!

Answer 1

您编写查询的方式，它不能在 ts_with 上使用索引.

要使用索引，条件必须是 ts_with <operator> <constant> 的形式，并且无法以这种方式重写查询。

所以你应该在 timezone(test_tz.tz, test_tz.ts_with) 上创建第二个索引.