如何在SQLite中按指定时区筛选对应周一的UTC存储日期

核心思路 #

SQLite没有内置的时区转换函数，要筛选Europe/London（或其他带夏令时的时区）的周一，需要手动处理UTC时间到目标时区的偏移，尤其要考虑夏令时的影响（伦敦夏令时比UTC快1小时，冬令时与UTC一致）。步骤如下：

1. 将UTC文本日期转换为Unix时间戳，便于计算偏移。
2. 根据年份计算目标时区当年夏令时的起止时间，判断当前UTC时间是否处于夏令时区间，确定偏移量。
3. 给UTC时间戳加上对应偏移量，得到目标时区的时间戳，再判断该时间是否为周一。

针对Europe/London的SQL实现 #

假设你的表名为your_table，日期列为Dates（格式需为SQLite可识别的YYYY-MM-DD或YYYY-MM-DD HH:MM:SS），可以用CTE简化逻辑：

WITH date_info AS (
    SELECT 
        Dates,
        strftime('%s', Dates) AS utc_ts, -- 转换为UTC时间戳
        strftime('%Y', Dates) AS year
    FROM your_table
),
dst_calc AS (
    SELECT 
        Dates,
        utc_ts,
        -- 计算当年夏令时开始时间：3月最后一个周日的01:00 UTC
        strftime('%s', year || '-03-31') - strftime('%w', year || '-03-31') * 86400 + 3600 AS dst_start,
        -- 计算当年夏令时结束时间：10月最后一个周日的01:00 UTC
        strftime('%s', year || '-10-31') - strftime('%w', year || '-10-31') * 86400 + 3600 AS dst_end
    FROM date_info
)
SELECT Dates
FROM dst_calc
WHERE 
    -- 给UTC时间戳加偏移后转成日期，判断是否为周一（%w=1代表周一）
    strftime('%w', utc_ts + CASE WHEN utc_ts BETWEEN dst_start AND dst_end THEN 3600 ELSE 0 END, 'unixepoch') = '1';

适配其他时区的调整方法 #

• 固定偏移时区（如UTC+2）：无需判断夏令时，直接给UTC时间戳加上对应秒数即可，例如：

  SELECT Dates
  FROM your_table
  WHERE strftime('%w', strftime('%s', Dates) + 7200, 'unixepoch') = '1';
  

• 带夏令时的其他时区：需要修改夏令时的起止规则（比如美国夏令时是3月第二个周日到11月第一个周日），调整dst_start和dst_end的计算逻辑即可。

注意事项 #

• 确保Dates列的格式符合SQLite的日期解析要求，否则strftime('%s', Dates)会返回NULL，导致计算失效。
• 夏令时规则可能会有地区性调整，需根据目标时区的最新规则修正计算逻辑。

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