I have a table of train rides, using the following sample code:

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');

When I run the following query against this data:

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;

It gives me the following output:

--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------

All of the times are shown in the CST time zone, which is based on the server timezone setting.

I would like to have the output reflect the local timezone for each origination or destination city using the AT TIME ZONE qualifier, perhaps using a lookup table like this:

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

How can I have each time reflect the local time zone? For example, in the first line, Chicago's departure would display as CST and New York's arrival would display as EST.

I had considered building dynamic queries using a plpgsql code block, but that would be very messy. I do hope that there is an easy, more elegant way to do this.

0 Answers