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.