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.
start_time_utc
,end_time_utc
) toevents
table?