Given a table with time range and TZ, is there a fast way to find rows where current time is within the time range while respecting TZ and DST?

Question

Let's say I want users to be able to create a record that contains a time range (but no date), specified in their own timezone. Then, at potentially any time, when something in my application happens, it needs to find all the records where the current time is within this time range, respecting the timezone stored with the record. Let's say I have a table setup like this:

schedules table

id | start_time | end_time | timezone
---------------------------------------
1  | 08:00:00   | 17:00:00 | US/Pacific
2  | 06:00:00   | 13:30:00 | US/Arizona
3  | 13:00:00   | 22:00:00 | US/Eastern
... etc

start_time and end_time is of type time without timezone. timezone is of type varchar, but let's pretend that it is guaranteed to be a value from the name column in the pg_timezone_names view.

I have a query that works to find the rows where the current time is between the row's start_time and end_time:

SELECT
    *
FROM
    schedules
WHERE
    schedules.start_time <= (CURRENT_TIMESTAMP AT TIME ZONE schedules.timezone)::time
    AND schedules.end_time >= (CURRENT_TIMESTAMP AT TIME ZONE schedules.timezone)::time;

Note the expression (CURRENT_TIMESTAMP AT TIME ZONE schedules.timezone)::time. This expression is intended to get the current local time of the row's specified timezone. As far as I'm aware, it should respect the current state of DST for that time zone since schedules.timezone is not an hour offset but a named timezone, which means Postgres knows if DST is on or off for that timezone at the given timestamp.

The problem now is that I think this query is guaranteed to do a full table scan no matter how I index the table, which means it will get noticeably slower the more this table grows. Is there a better way to do this that retains the ability to automatically adjust for DST in any timezone without also needing to a full table scan?

Answer 1

Interesting question! The technical reason why your query cannot use an index is that both sides of the comparison contain a column reference, that is, they both depend on the current row. You need something fairly constant for an index scan.

But I think that that is not just a mistake in the way you wrote the query (the query is correct!): what you want is fundamentally impossible. The meaning of 13:00:00 in time zone US/Eastern depends on the current date. To use an index, you would have to compare "13:00:00 today in time zone US/Eastern" to the current timestamp, so you'd have to index that expression. But an expression that you index cannot change its meaning with the current date.

The only workaround I can think of is to create a materialized view like this:

CREATE MATERIALIZED VIEW schedules_today AS
SELECT id,
       (start_time + current_date) AT TIME ZONE timezone AS start_timestamp,
       (end_time + current_date) AT TIME ZONE timezone AS end_timestamp
FROM schedules;

You can create indexes on start_timestamp and end_timestamp to speed up the query. You'd have to refresh the materialized view every day after midnight.

You might get better performance if you use a range type instead:

CREATE MATERIALIZED VIEW schedules_today AS
SELECT id,
       tstzrange(
          (start_time + current_date) AT TIME ZONE timezone,
          (end_time + current_date) AT TIME ZONE timezone,
          '[]'
       ) AS start_end
FROM schedules;

CREATE INDEX ON schedules_today USING gist (start_end);

The GiST index would speed uf the following query:

SELECT * FROM schedules_today
WHERE start_end @> current_timestamp;