带时区时间范围表：如何高效查询当前时间匹配记录（支持DST）

需求是查询所有当前时间（遵循记录自身时区规则）处于其时间范围的无日期时间记录，现有查询虽能正确处理夏令时，但因每行需计算时区转换后的本地时间，触发全表扫描，数据量增长后性能显著下降。

表结构（PostgreSQL）：

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

其中start_time、end_time为time without timezone类型，timezone取值来自pg_timezone_names.name。

现有查询：

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;

以下方案均保留夏令时自动适配能力，同时避免全表扫描：

方案1：预计算UTC时间范围+定时更新 #

思路 #

将每条记录的当日时间范围转换为UTC时间戳存储，每天定时更新以适配夏令时偏移变化，查询时直接匹配当前UTC时间。

步骤 #

1. 新增两个timestamp without timezone字段存储UTC时间范围：

   ALTER TABLE schedules ADD COLUMN start_utc timestamp without timezone;
   ALTER TABLE schedules ADD COLUMN end_utc timestamp without timezone;
   

2. 创建每日定时任务（如用pg_cron）更新UTC时间：

   -- 每天UTC凌晨更新所有记录的UTC时间范围
   SELECT cron.schedule(
       'daily-update-schedule-utc',
       '0 0 * * *',
       $$
       UPDATE schedules
       SET start_utc = (CURRENT_DATE AT TIME ZONE timezone + start_time) AT TIME ZONE 'UTC',
           end_utc = (CURRENT_DATE AT TIME ZONE timezone + end_time) AT TIME ZONE 'UTC';
       $$
   );
   

3. 创建复合索引：

   CREATE INDEX idx_schedules_utc_range ON schedules (start_utc, end_utc);
   

4. 查询语句简化为：

   SELECT * FROM schedules
   WHERE CURRENT_TIMESTAMP BETWEEN start_utc AND end_utc;
   

优缺点 #

• 优点：查询性能极高，索引命中率100%
• 缺点：依赖定时任务，夏令时切换当日凌晨有极小概率出现数据偏差（可通过调整任务执行时间规避）

方案2：时区分区表+分区索引 #

思路 #

按时区对表做分区，每个时区对应独立分区，在分区内创建时间范围索引，查询时仅扫描符合条件的时区分区。

步骤 #

1. 创建分区主表：

   CREATE TABLE schedules (
       id INT,
       start_time TIME WITHOUT TIME ZONE,
       end_time TIME WITHOUT TIME ZONE,
       timezone VARCHAR
   ) PARTITION BY LIST (timezone);
   

2. 为常用时区创建分区（按需扩展）：

   CREATE TABLE schedules_us_pacific PARTITION OF schedules FOR VALUES IN ('US/Pacific');
   CREATE TABLE schedules_us_arizona PARTITION OF schedules FOR VALUES IN ('US/Arizona');
   CREATE TABLE schedules_us_eastern PARTITION OF schedules FOR VALUES IN ('US/Eastern');
   

3. 为每个分区创建时间索引：

   CREATE INDEX idx_sched_pacific_time ON schedules_us_pacific (start_time, end_time);
   CREATE INDEX idx_sched_arizona_time ON schedules_us_arizona (start_time, end_time);
   CREATE INDEX idx_sched_eastern_time ON schedules_us_eastern (start_time, end_time);
   

4. 查询时先筛选符合条件的时区，再关联分区表：

   WITH eligible_tz AS (
       SELECT 
           name AS timezone,
           (CURRENT_TIMESTAMP AT TIME ZONE name)::time AS local_now
       FROM pg_timezone_names
   )
   SELECT s.*
   FROM schedules s
   JOIN eligible_tz et ON s.timezone = et.timezone
   WHERE s.start_time <= et.local_now AND s.end_time >= et.local_now;
   

优缺点 #

• 优点：无需定时任务，自动适配夏令时，分区内查询效率高
• 缺点：时区数量过多时，分区管理成本较高

方案3：复合索引+时区预筛选 #

思路 #

先从pg_timezone_names筛选出当前本地时间符合条件的时区，再通过复合索引快速定位对应时区的记录，避免全表扫描。

步骤 #

1. 创建(timezone, start_time, end_time)复合索引：

   CREATE INDEX idx_schedules_tz_time ON schedules (timezone, start_time, end_time);
   

2. 使用CTE预筛选符合条件的时区，再关联查询：

   WITH eligible_tz AS (
       SELECT 
           name AS timezone,
           (CURRENT_TIMESTAMP AT TIME ZONE name)::time AS local_now
       FROM pg_timezone_names
   )
   SELECT s.*
   FROM schedules s
   JOIN eligible_tz et 
       ON s.timezone = et.timezone
       AND s.start_time <= et.local_now
       AND s.end_time >= et.local_now;
   

优缺点 #

• 优点：无需定时任务，自动适配夏令时，索引利用充分，管理成本低
• 缺点：需依赖pg_timezone_names视图的查询效率（视图仅数百条记录，无性能问题）

内容的提问来源于stack exchange，提问作者Blargel