SQL报错：No Matching Signature for Operator BETWEEN，求语句修正

问题背景 #

数据集包含Id、ActivityHour、StepTotal三个字段，其中ActivityHour格式为YYYY-MM-DD HH:MM:SS。需要筛选满足以下条件的记录，并按Id排序：

• 日期在2016-04-12至2016-05-12之间
• 时间在08:00:00 UTC至15:00:00 UTC之间
• StepTotal小于50

尝试用SUBSTR拆分日期时间时触发报错："No Matching Signature for Operator BETWEEN"，原SQL语句如下：

SELECT Id, ActivityHour, StepTotal,
SUBSTR(ActivityHour, 11, 23) AS Time,
FROM `dataset` AS Time
WHERE ActivityHour BETWEEN '2016-04-12' AND '2016-05-12'
AND Time BETWEEN '08:00:00 UTC' AND '15:00:00 UTC' 
AND StepTotal < 50
ORDER BY Id, ActivityHour

错误原因 #

1. 别名冲突：将表别名设为Time，与SELECT子句中定义的列别名Time重名，导致WHERE子句里的Time被识别为表而非计算出的时间列，类型不匹配引发BETWEEN运算符报错。
2. SUBSTR参数错误：SUBSTR(ActivityHour,11,23)的长度参数不合理——ActivityHour的时间部分仅8位（HH:MM:SS），若字段不含UTC，取23位会提取多余内容，造成类型不匹配。
3. 日期筛选逻辑漏洞：直接用字符串ActivityHour BETWEEN '2016-04-12' AND '2016-05-12'会漏掉2016-05-12当天非0点的记录，因为字符串比较仅到日期部分就截止。

修正后的SQL #

方案一：字符串拆分法（适合纯字符串格式的时间字段） #

SELECT Id, ActivityHour, StepTotal,
  -- 若字段含UTC，将长度改为13（对应'HH:MM:SS UTC'）
  SUBSTR(ActivityHour, 11, 8) AS TimePart
FROM `dataset`
WHERE 
  -- 用DATE函数提取日期，确保覆盖2016-05-12全天记录
  DATE(ActivityHour) BETWEEN '2016-04-12' AND '2016-05-12'
  -- 若字段含UTC，将匹配值改为'08:00:00 UTC'和'15:00:00 UTC'
  AND SUBSTR(ActivityHour, 11, 8) BETWEEN '08:00:00' AND '15:00:00'
  AND StepTotal < 50
ORDER BY Id, ActivityHour

方案二：时间戳函数法（更规范，适合支持时间类型的数据库如BigQuery） #

SELECT Id, ActivityHour, StepTotal,
  FORMAT_TIMESTAMP('%H:%M:%S', TIMESTAMP(ActivityHour)) AS TimePart
FROM `dataset`
WHERE 
  -- 精确指定时间范围，包含2016-05-12 23:59:59之前的所有记录
  TIMESTAMP(ActivityHour) BETWEEN TIMESTAMP('2016-04-12 00:00:00 UTC') AND TIMESTAMP('2016-05-12 23:59:59 UTC')
  -- 直接提取小时数判断范围，避免字符串格式问题
  AND EXTRACT(HOUR FROM TIMESTAMP(ActivityHour)) BETWEEN 8 AND 15
  AND StepTotal < 50
ORDER BY Id, ActivityHour

修正要点 #

• 删除冲突的表别名Time，避免列与表的识别混淆。
• 调整SUBSTR参数，根据字段是否含UTC灵活提取时间部分。
• 用DATE()或TIMESTAMP()函数处理日期筛选，确保逻辑准确，覆盖目标日期的全天记录。
• 时间范围判断优先使用时间函数提取小时数，减少字符串格式不一致带来的问题。

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