Postgres时区查询

Question

我有一张公司表，每个公司都有独立的时区。 例如 - 公司1的时区为UTC+10， 公司2的时区为UTC+2。

表companies有一个字段time_zone，存储区域缩写，例如America/Los_Angeles（如果需要，可以添加额外的字段来存储与UTC的偏移值）。

并且有一个请求表requests，其中有一个start_date字段，存储没有时区的TIMESTAMP（UTC-0） 例如 -

id | company_id | start_date (utc-0)
------------------------------------
1  | 1          | 21-03-16 02-00      // added for company  `21-03-16 12-00`
2  | 2          | 21-03-16 23-00      // added for company  `22-03-16 01-00`
3  | 1          | 20-03-16 13-00      // added for company  `20-03-16 23-00`
4  | 1          | 21-03-16 23-00      // added for company  `22-03-16 09-00

我想选择从21-03-16 00-00到21-03-16 23-59的记录，考虑每个公司的时区。

但是，如果我使用以下命令 -

select * from request where start_date between '2016-03-21 00:00:00.000000' AND '2016-03-21 23:59:59.999999'

我会得到id为2和4的请求。

但是，这些请求实际上是在22-03-16添加的，对于每个公司而言都是如此。

有什么建议可以通过一次选择解决这个问题吗？非常感谢。

- Artoym P

---

喜欢 request.start_date AT TIME ZONE companies.time_zone BETWEEN '2016-03-21 00:00:00Z' AND '2016-03-21 23:59:59.999999Z' 吗？当每一行都有一个PostgreSQL接受的时区在 time_zone 中时，这将起作用。您可以定义一个检查约束条件，例如 CHECK ((timestamp '2000-01-01 00:00:00' AT TIME ZONE time_zone) IS NOT NULL) 来验证 time_zone。 - pozs

Answer 1

我不确定我是否正确理解了你的问题，你可能需要澄清一下。

但我认为加入存储有时区信息的公司应该可以解决这个问题:

SELECT r.*
FROM request r
   JOIN companies c ON c.id = r.company_id
WHERE r.start_date BETWEEN '2016-03-21 00:00:00.000000'
                           AT TIME ZONE c.time_zone
                       AND '2016-03-21 23:59:59.999999'
                           AT TIME ZONE c.time_zone;

- Laurenz Albe