Postgresql – How to maintain a “virtual column” which is composed of data from 2 tables

postgresql

I have these two tables:

CREATE TABLE events (
    id integer NOT NULL,
    user_id integer NOT NULL,
    start_time time without time zone,
    end_time time without time zone
);

CREATE TABLE users (
    id integer NOT NULL,
    timezone_string character varying
);

The users change their timezone from time to time, but maintain the same schedule relative to the timezone where they are.

I want to be able to run this query:

select * from events where start_time >= '12:00' and end_time < '12:00';

where '12:00' is UTC.

But I want start_time and end_time to be adjust for each user's individual timezone. I can join with the users table and run a function to make the adjustment…

select * from events inner join users on (user_id = users.id)
where
  start_time::time at time zone 'UTC' at time zone timezone_string >= '12:00'
  and
  end_time::time at time zone 'UTC' at time zone timezone_string < '12:00';

…but that means each time I do this query I have to iterate over the entirety of both tables.

What are the ways I can precalculate the timezone adjustment, and auto-update the adjustment when data in either table changes? A materialized view needs to be completely rebuilt on each refresh, so that's out of the question. The best idea I have so far is to simply make a cache table that holds the adjusted data, and put triggers on the appropriate schedule and user columns to maintain the cache table.

Best Answer

You probably want to store event dates as TIMESTAMP WITH TIME ZONE because just the HH:mm:ss+TZ time without the corresponding date might be ambiguous or misleading -- for example, America/New_York timezone points to two different times during the summer and the winter due to daylight saving changes. Same goes for times that you query against (12:00):

CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR NOT NULL,
    timezone VARCHAR NOT NULL
);

CREATE TABLE events (
    id SERIAL PRIMARY KEY NOT NULL,
    user_id INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    start_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    end_timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX events_start_timestamp ON events (start_timestamp);
CREATE INDEX events_end_timestamp ON events (end_timestamp);

INSERT INTO users (name, timezone) VALUES
    ('Linas', 'GMT+2');
INSERT INTO events (user_id, start_timestamp, end_timestamp) VALUES
    (1, '2018-02-27 12:00:00-03', '2018-02-27 15:00:00-03');

With that in place, one can query against precalculated timestamps pretty easily (correct timezone conversion will be done behind the scenes by the database itself) and convert the resulting timestamp to user's timezone too:

SELECT users.id,
       users.name,
       (events.start_timestamp::TIMESTAMP AT TIME ZONE users.timezone) AS user_start_timestamp
FROM events
    INNER JOIN users ON events.user_id = users.id
WHERE start_timestamp >= '2018-02-27 11:00:00-03'::TIMESTAMP WITH TIME ZONE
  AND end_timestamp < '2018-02-27 16:00:00-03'::TIMESTAMP WITH TIME ZONE;

(SQL Fiddle)

Related Question