postgresql - 在 SELECT 中显示每个位置的本地时区

Question

我有一张火车行程表，使用以下示例代码:

CREATE TABLE train_rides (
    trip_id bigserial PRIMARY KEY,
    origination text NOT NULL,
    destination text NOT NULL,
    departure timestamp with time zone NOT NULL,
    arrival timestamp with time zone NOT NULL
);

INSERT INTO train_rides (origination, destination, departure, arrival)
VALUES
    ('Chicago', 'New York', '2017-11-13 21:30 CST', '2017-11-14 18:23 EST'),
    ('New York', 'New Orleans', '2017-11-15 14:15 EST', '2017-11-16 19:32 CST'),
    ('New Orleans', 'Los Angeles', '2017-11-17 13:45 CST', '2017-11-18 9:00 PST'),
    ('Los Angeles', 'San Francisco', '2017-11-19 10:10 PST', '2017-11-19 21:24 PST'),
    ('San Francisco', 'Denver', '2017-11-20 9:10 PST', '2017-11-21 18:38 MST'),
    ('Denver', 'Chicago', '2017-11-22 19:10 MST', '2017-11-23 14:50 CST');

当我针对此数据运行以下查询时:

SELECT origination || ' to ' || destination AS segment,
       to_char(departure, 'YYYY-MM-DD HH12:MI a.m. TZ') AS departure,
       to_char(arrival, 'YYYY-MM-DD HH12:MI a.m. TZ') AS arrival
FROM train_rides;

它给了我以下输出:

--------------------------------------------------------------------------------------
| segment                    | departure                 | arrival                   |
--------------------------------------------------------------------------------------
| Chicago to New York        | 2017-11-13 09:30 p.m. CST | 2017-11-14 05:23 p.m. CST |
| New York to New Orleans    | 2017-11-15 01:15 p.m. CST | 2017-11-16 07:32 p.m. CST |
| New Orleans to Los Angeles | 2017-11-17 01:45 p.m. CST | 2017-11-18 11:00 a.m. CST |
| San Francisco to Denver    | 2017-11-20 11:10 a.m. CST | 2017-11-21 07:38 p.m. CST |
| Denver to Chicago          | 2017-11-22 08:10 p.m. CST | 2017-11-23 02:50 p.m. CST |
--------------------------------------------------------------------------------------

所有时间均以CST时区显示，以服务器时区设置为准。

首选输出

我想让输出反射(reflect)每个始发地或目的地城市的本地时区，如下所示:

--------------------------------------------------------------------------------------
| segment                    | departure                 | arrival                   |
--------------------------------------------------------------------------------------
| Chicago to New York        | 2017-11-13 09:30 p.m. CST | 2017-11-14 06:23 p.m. EST |
| New York to New Orleans    | 2017-11-15 02:15 p.m. EST | 2017-11-16 07:32 p.m. CST |
| New Orleans to Los Angeles | 2017-11-17 01:45 p.m. CST | 2017-11-18 09:00 a.m. PST |
| San Francisco to Denver    | 2017-11-20 09:10 a.m. PST | 2017-11-21 06:38 p.m. MST |
| Denver to Chicago          | 2017-11-22 07:10 p.m. MST | 2017-11-23 02:50 p.m. CST |
--------------------------------------------------------------------------------------

如何让每次都反射(reflect)本地时区？例如，在上面的第一行中，芝加哥的出发将显示为 CST，纽约的到达将显示为 EST。

一个可能的解决方案是使用 AT TIME ZONE 限定符，也许使用这样的查找表:

----------------------------
| city          | local_tz |
----------------------------
| Chicago       | CST      |
| Denver        | MST      |
| Los Angeles   | PST      |
| New Orleans   | CST      |
| New York      | EST      |
| San Francisco | PST      |
----------------------------

我曾考虑使用 plpgsql 代码块构建动态查询，但那会非常困惑。我确实希望有一种简单、更优雅的方法来做到这一点。

Answer 1

我找到了获得预期结果的方法；这不是最优雅的方法，但确实有效。

首先，我在 train_rides 表中添加了两个额外的列:

-- Add columns to hold local times 
ALTER TABLE train_rides
    ADD COLUMN departure_local TIMESTAMP WITHOUT TIME ZONE,
    ADD COLUMN arrival_local TIMESTAMP WITHOUT TIME ZONE;

然后我使用一堆代码(使用不太优雅的蛮力方法)填充本地出发列:

-- Update departures to reflect local times
UPDATE train_rides
    SET departure_local = departure AT TIME ZONE 'US/Central'
    WHERE origination = 'Chicago';
UPDATE train_rides
   SET departure_local = departure AT TIME ZONE 'US/Mountain'
   WHERE origination = 'Denver';
UPDATE train_rides
    SET departure_local = departure AT TIME ZONE 'US/Pacific'
    WHERE origination = 'Los Angeles';
UPDATE train_rides
    SET departure_local = departure AT TIME ZONE 'US/Central'
    WHERE origination = 'New Orleans';
UPDATE train_rides
   SET departure_local = departure AT TIME ZONE 'US/Eastern'
   WHERE origination = 'New York';
UPDATE train_rides
   SET departure_local = departure AT TIME ZONE 'US/Pacific'
   WHERE origination = 'San Francisco';

接下来，我以相同的方式填充本地到达:

-- Update arrivals to reflect local times
UPDATE train_rides
    SET arrival_local = arrival AT TIME ZONE 'US/Central'
    WHERE origination = 'Chicago';
UPDATE train_rides
   SET arrival_local = arrival AT TIME ZONE 'US/Mountain'
   WHERE origination = 'Denver';
UPDATE train_rides
    SET arrival_local = arrival AT TIME ZONE 'US/Pacific'
    WHERE origination = 'Los Angeles';
UPDATE train_rides
    SET arrival_local = arrival AT TIME ZONE 'US/Central'
    WHERE origination = 'New Orleans';
UPDATE train_rides
   SET arrival_local = arrival AT TIME ZONE 'US/Eastern'
   WHERE origination = 'New York';
UPDATE train_rides
   SET arrival_local = arrival AT TIME ZONE 'US/Pacific'
   WHERE origination = 'San Francisco';

最后，我可以使用一些 SQL 魔法来获得最终结果:

SELECT origination || ' to ' || destination AS segment,
       to_char(departure_local, 'YYYY-MM-DD HH12:MI a.m. ') ||
       (SELECT local_tz from local_timezones tz
           WHERE tr.origination = tz.city) AS departure,
       to_char(arrival_local, 'YYYY-MM-DD HH12:MI a.m. ') ||
       (SELECT local_tz from local_timezones tz
           WHERE tr.destination = tz.city) AS arrival
FROM train_rides tr;

如果有一种方法可以使用查找动态填充 AT TIME ZONE 参数，那么所有这些 UPDATE 语句都可以减少到只有两个。这种蛮力方法不能很好地扩展，因为我们必须为每个可能的城市手动编码。

除非，也就是说，有人知道更好的方法。