时区转换SQL查询异常：为何仅输出≥2023-07-05 20:00:00的结果？

问题根源 #

你的WHERE子句存在语法错误：你把时区转换代码'America/Los_Angeles' AT TIME ZONE 'America/New_York'直接插入到CONCAT的参数列表中，导致拼接出的字符串完全不是合法的时间格式。错误的字符串经过CAST转换后，得到的时间值严重偏离预期，最终只筛选出了相当于纽约时间2023-07-05 20:00:00之后的数据。

修正后的SQL语句 #

写法一：直接修正WHERE子句 #

SELECT DATE_FORMAT(
  CAST(
    CONCAT(
      CAST(year as VARCHAR),
      '-',
      CAST(month as VARCHAR),
      '-',
      CAST(day as VARCHAR),
      ' ',
      LPAD(CAST(hour as VARCHAR), 2, '0'), ':00:00'
    ) as timestamp) AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York', 
  '%Y-%m-%d %H:%i:%s'
) as t 
FROM table
WHERE DATE(
  CAST(
    CONCAT(
      CAST(year as VARCHAR),
      '-',
      CAST(month as VARCHAR),
      '-',
      CAST(day as VARCHAR),
      ' ',
      LPAD(CAST(hour as VARCHAR), 2, '0'), ':00:00'
    ) as timestamp) AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York'
) >= '2023-07-05'

写法二：用子查询简化重复逻辑 #

SELECT DATE_FORMAT(ny_time, '%Y-%m-%d %H:%i:%s') as t
FROM (
  SELECT 
    CAST(
      CONCAT(
        CAST(year as VARCHAR), '-', CAST(month as VARCHAR), '-', CAST(day as VARCHAR),
        ' ', LPAD(CAST(hour as VARCHAR), 2, '0'), ':00:00'
      ) as timestamp) AT TIME ZONE 'America/Los_Angeles' AT TIME ZONE 'America/New_York' as ny_time
  FROM table
) sub
WHERE DATE(ny_time) >= '2023-07-05'

说明 #

两种写法都是先将表中的year/month/day/hour拼接成PDT时区的时间戳，再转换为纽约时区的时间，最后基于转换后的日期进行筛选，这样就能准确获取纽约时间≥2023-07-05的所有数据。

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