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.
Preferred Output
I would like to have the output reflect the local timezone for each origination or destination city, like so:
--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------
How can I have each time reflect the local time zone? For example, in the first line above, Chicago's departure would display as CST and New York's arrival would display as EST.
One possible solution would be to use 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 |
----------------------------
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.
Today I learned about the power of SQL subqueries, and the light bulb went off in my head. What follows are two better approaches.
Both of these approaches make use of the following lookup table named trains_local_timezones:
-----------------------------------------
| city | timezone | tz_abbr |
-----------------------------------------
| Chicago | US/Central | CST |
| Denver | US/Mountain | MST |
| Los Angeles | US/Pacific | PST |
| New Orleans | US/Central | CST |
| New York | US/Eastern | EST |
| San Francisco | US/Pacific | PST |
-----------------------------------------
-- 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
(SELECT timezone from trains_local_timezones tz
WHERE origination = tz.city);
-- Update arrivals to reflect local times
UPDATE train_rides
SET arrival_local = arrival AT TIME ZONE
(SELECT timezone from trains_local_timezones tz
WHERE destination = tz.city);
SELECT origination || ' to ' || destination AS segment,
to_char(departure_local, 'YYYY-MM-DD HH12:MI AM ') ||
(SELECT tz_abbr from trains_local_timezones tz
WHERE tr.origination = tz.city) AS local_departure,
to_char(arrival_local, 'YYYY-MM-DD HH12:MI AM ') ||
(SELECT tz_abbr from trains_local_timezones tz
WHERE tr.destination = tz.city) AS local_arrival
FROM train_rides tr;
SELECT origination || ' to ' || destination AS segment,
to_char(departure AT TIME ZONE
(SELECT timezone from trains_local_timezones tz
WHERE origination = tz.city), 'YYYY-MM-DD HH12:MI AM ') ||
(SELECT tz_abbr from trains_local_timezones tz
WHERE tr.origination = tz.city) AS local_departure,
to_char(arrival AT TIME ZONE
(SELECT timezone from trains_local_timezones tz
WHERE destination = tz.city), 'YYYY-MM-DD HH12:MI AM ') ||
(SELECT tz_abbr from trains_local_timezones tz
WHERE tr.destination = tz.city) AS local_arrival
FROM train_rides tr;