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