Postgres时间与时区的比较

Question

今天我遇到了一个奇怪的postgres行为。让我解释一下:

这是我要做的桌子。

=># d planning_time_slot 
Table "public.planning_time_slot"
Column    |           Type            | Collation | Nullable |                    Default                     
-------------+---------------------------+-----------+----------+------------------------------------------------
id          | integer                   |           | not null | nextval('planning_time_slot_id_seq'::regclass)
planning_id | integer                   |           | not null | 
day         | character varying(255)    |           | not null | 
start_time  | time(0) without time zone |           | not null | 
end_time    | time(0) without time zone |           | not null | 
day_id      | integer                   |           | not null | 0
Indexes:
"planning_time_slot_pkey" PRIMARY KEY, btree (id)
"idx_a9e3f3493d865311" btree (planning_id)
Foreign-key constraints:
"fk_a9e3f3493d865311" FOREIGN KEY (planning_id) REFERENCES planning(id)

我想做的是这样的:

select * from planning_time_slot where start_time > (CURRENT_TIME AT TIME ZONE 'Europe/Paris');

但是看起来postgres比较的是时区转换之前的时间。以下是我的测试:

=># select * from planning_time_slot where start_time > (CURRENT_TIME AT TIME ZONE 'Europe/Paris');
id  | planning_id | day | start_time | end_time | day_id 
-----+-------------+-----+------------+----------+--------
157 |           6 | su  | 16:00:00   | 16:30:00 |      0
(1 row)
=># select (CURRENT_TIME AT TIME ZONE 'Europe/Paris');
timezone      
--------------------
16:35:48.591002+02
(1 row)

当我尝试很多条目时，似乎比较是在start_time和CURRENT_TIME之间完成的，没有时区转换。

我也试过了

select * from planning_time_slot where start_time > timezone('Europe/Paris', CURRENT_TIME);

结果完全相同。

我还试图将列类型更改为时区的time(0)。它产生完全相同的结果。

最后一点很重要。我真的需要设置我想要的时区，因为稍后我将根据其他东西动态地更改它。所以不会每次都是"欧洲/巴黎"。

谁有线索或提示吗?

psql (PostgreSQL) 11.2 (Debian 11.2 - -1. pgdg90 + 1)

Answer 1

(CURRENT_TIME AT TIME ZONE 'Europe/Paris')为17:52:17.872082+02。但内部是15:52:17.872082+00。time和timmetz(带时区的时间)都存储为UTC，唯一的区别是timmetz存储与时区。更改时区不会更改它所代表的时间点。

所以当你把它和时间比较时。

# select '17:00:00'::time < '17:52:17+02'::timetz;
?column? 
----------
f

That is really…

# select '17:00:00'::time < '15:52:17'::time;
?column? 
----------
f

将timemetz转换为时间将去掉时区。

test=# select (CURRENT_TIME AT TIME ZONE 'Europe/Paris')::time;
timezone     
-----------------
17:55:57.099863
(1 row)
test=# select '17:00:00' < (CURRENT_TIME AT TIME ZONE 'Europe/Paris')::time;
?column? 
----------
t

注意，这种比较只有在您想要存储根据墙上的时钟在17:00发生的事情的概念时才有意义。例如，如果你有一款手机游戏，其中活动"在17:00"开始;即用户所在位置17:00。这被称为"浮动时区"。

---

• 假设day是"星期几"，我建议将其存储为整数。比较和本地化更容易。
• 考虑单个timerange，而不是单独的开始和结束时间。然后可以使用范围操作符。

Answer 2

我认为你有更深层次的问题。

你有一天，开始时间和结束时间，但没有时区的概念。因此，这将意味着不同的东西取决于观察者的时区。

我认为你应该添加一个tz列来存储该信息所在的时区。然后你可以像这样得到开始时间:

WHERE (day + start_time) AT TIME ZONE tz > current_timestamp