特定时区的索引时间戳
问题描述:
我的控制台是PST。 数据库服务器和存储的时间是GMT。特定时区的索引时间戳
我不必像这样运行查询:
SELECT x,y,z
FROM tbl_msg
WHERE (msg_datetime AT TIME ZONE 'BST') BETWEEN '2016-11-21'::date and '2016-11-22'::date;
索引101表示,在msg_datetime执行此操作,现在将避免指数,这是我所看到的。
所以我需要一个索引解决方案的建议。
我可以索引这个时区吗?或改变这个查询,以便它在BST中查询这些时间,转换为GMT?
答
您应该有msg_datetime
列timestamp with time zone
(或更短的别名timestamptz
)与正常的索引。
然后,为了获取数据,这2天,你应该:
set timezone 'Europe/London'; -- once, on connection start
SELECT x,y,z
FROM tbl_msg
WHERE
msg_datetime>='2016-11-21 00:00:00'
and
msg_datetime<'2016-11-23 00:00:00';
你不应该使用普通timestamp
,因为它存储字面日期和时间没有有关哪些时区,它实际上的意思。一个timestamp with time zone
类型会自动将客户端配置的时间转换为内部表示(使用UTC)并返回。您还可以使用例如'2016-11-23 00:00:00 Asia/Tokyo'
从非默认时区表示时间戳。
此外,您不应该使用BST
- 因为您需要在冬天使用GMT
并记住何时使用哪个。您应该使用“欧洲/伦敦”或其他“城市”时区(list),夏季和冬季都适用。
这是有趣的设置连接时区。我只看到了“AT TIME ZONE'XXX'”。 这可能是答案,它允许我在BST时间传递并将其转换为GMT/UTC,而不必将GMT/UTC转换为BST。 –
我看到你提到不使用BST。这就是我必须像这样施放的全部原因。用户是BST,并且想要使用BST术语在应用程序中进行搜索。 这就是为什么我将数据转换为不同的时区,因为我们需要允许发生时区感知转换,因此如果需要,需要考虑该小时 –
您错了 - 您没有BST用户。你可能有“欧洲/伦敦”用户,它使用BST时区(UTC + 1)半年(因为它不是今天,因为它在北半球是冬天),以及另一个半年(与今天一样)的GMT时区。例如,您可能会使用夏季使用CEST(UTC + 2)和冬季使用CET(也是UTC + 1)的“欧洲/华沙”客户端。 – Tometzky