PostgreSQL中如何匹配不同时区用户的指定本地执行时间？

数据库中仅以字符串形式存储用户本地时间（例如20:00），需要实现：每天在用户本地时区的指定时刻，自动执行对应任务。服务器运行在UTC时区，每小时执行一次查询，筛选出当前需要触发任务的用户（用户的时区信息已单独存储）。

最初尝试的核心查询逻辑存在偏差：

SELECT '20:00'::time = '12:00'::time AT TIME ZONE 'PST'

这里'12:00'::time AT TIME ZONE 'PST'返回04:00:00-08，本质是将UTC时间转换为PST时间，和需求（把UTC服务器时间转换为用户本地时间后，匹配存储的本地时间字符串）完全不符。

尝试用字符串比较时又遇到函数不支持的报错：

SELECT to_char('20:00'::time AT TIME ZONE 'PST', 'HH24:MI') = '12:00'

报错信息：function to_char(time with time zone, unknown) does not exist，原因是PostgreSQL的to_char函数不支持带时区的time类型。

方案1：提取时间分量拼接比较 #

通过EXTRACT提取转换后的时间的小时、分钟分量，补零后拼接成标准时间格式，再与存储的字符串匹配：

先验证提取逻辑：

SELECT EXTRACT(HOUR FROM TIME '20:00'::time AT TIME ZONE 'PST');
-- 输出结果：12
SELECT EXTRACT(MINUTE FROM TIME '20:00'::time AT TIME ZONE 'PST');
-- 输出结果：0

实际业务中需用服务器当前UTC时间（CURRENT_TIMESTAMP），完整SQL如下：

SELECT CONCAT(
  LPAD(EXTRACT(HOUR FROM CURRENT_TIMESTAMP AT TIME ZONE user_timezone)::TEXT, 2, '0'),
  ':',
  LPAD(EXTRACT(MINUTE FROM CURRENT_TIMESTAMP AT TIME ZONE user_timezone)::TEXT, 2, '0')
) = user_scheduled_time
FROM users;

注：user_timezone为存储用户时区的字段，user_scheduled_time为存储本地时间字符串的字段。

方案2：转换为本地timestamp后格式化（更简洁） #

利用CURRENT_TIMESTAMP AT TIME ZONE 时区会返回该时区的timestamp without time zone类型，而to_char支持此类型，直接格式化后比较：

SELECT TO_CHAR(CURRENT_TIMESTAMP AT TIME ZONE user_timezone, 'HH24:MI') = user_scheduled_time
FROM users;

这个方法更简洁高效，完美避开了之前的函数不支持问题，同时直接实现了需求：将UTC时间转换为用户本地时间的时分格式，与存储的字符串匹配。

内容的提问来源于stack exchange，提问作者Michal Krejčí