基于关联的记录区域字段转换输入时区

Question

我有一个与地址表的一对一关联的BUS_STOPS表，地址具有时区属性。

现在在搜索停止时间时，说所有公共汽车在晚上7点出发，这必须在当地时间下午7点出发的公共汽车搜索停靠点，即，

对于每个BUS_STOP，我需要根据地址记录的时区域将7pm转换为总线停靠站的本地时区。

我将如何使用SQL(用于Postgres(

Answer 1

由于您的问题不是很具体，我会回答一个示例，该示例应该如何完成。您必须为您的目的调整示例；例如，我不希望您将出发时间存储为timestamp with time zone。

CREATE TABLE address (
   id integer PRIMARY KEY,
   timezone text NOT NULL
);
INSERT INTO address VALUES
   (1, 'America/Los_Angeles'),
   (2, 'America/Chihuahua');
CREATE TABLE bus_stop (
   id integer PRIMARY KEY,
   depart TIMESTAMP WITH TIME ZONE NOT NULL,
   address_id INTEGER NOT NULL
      REFERENCES address(id)
);
INSERT INTO bus_stop VALUES
   (1, '2017-06-27 19:00:00 America/Los_Angeles', 1),
   (2, '2017-06-27 18:00:00 America/Los_Angeles', 1),
   (3, '2017-06-27 18:00:00 America/Los_Angeles', 2);

现在，我们想知道当地时间19:00出发的公共汽车将在哪个公共汽车站出发：

SELECT b.id
FROM bus_stop b
   JOIN address a
      ON b.address_id = a.id
WHERE (b.depart AT TIME ZONE a.timezone) = TIMESTAMP '2017-06-27 19:00:00';
┌────┐
│ id │
├────┤
│  1 │
│  3 │
└────┘
(2 rows)

Answer 2

使用 AT TIME ZONE从时区转换为另一个：

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'BRT';

更多信息。