特定时区的索引时间戳

Question

我的控制台是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？

Answer 1

您应该有类型为msg_datetime的timestamp with time zone列(或更短的别名timestamptz)，并具有正常索引。

然后，要获得这两天的数据，您应该：

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，并且记住什么时候使用哪个。你应该使用‘欧洲/伦敦’或其他“城市”时区(列表)，这些时区在夏季和冬季都是正确的。