Impala查询毫秒级int时间字段筛选近30天记录的SQL写法问题

原有SQL无法返回结果，由3个语法问题导致：

• 子句顺序错误：SQL标准执行顺序为FROM -> WHERE -> SELECT -> LIMIT，原写法把LIMIT 100放在WHERE之前，不符合语法规范
• 别名引用错误：WHERE子句执行优先级高于SELECT，无法直接引用SELECT中定义的Completed_Date别名
• 时间值格式错误：筛选条件中的2022-05-09 00:00:00.000未添加字符串单引号，数据库会将其识别为算术表达式计算，不会解析为时间类型值

优先选择直接计算毫秒时间戳做数值匹配的方案，不需要对表中每行的last_modified_date做格式转换，如果该字段建有索引可以直接命中，大数据量下查询效率远高于格式转换后比较的方案。
近30天数据的查询代码如下（适配当前使用的Hive/Spark SQL语法）：

SELECT  
  last_modified_date, 
  from_timestamp(CAST(CAST(last_modified_date AS decimal(30,0))/1000 AS timestamp), "yyyy-MM-dd HH:mm:ss.SSS") AS "Completed_Date" 
FROM helix_access.chg_infrastructure_change 
-- 计算逻辑：当前时间毫秒级时间戳 减去 30天对应的总毫秒数
WHERE last_modified_date >= unix_timestamp() * 1000 - 30*24*60*60*1000
-- 如果需要指定固定时间点筛选，替换为下面的条件即可
-- WHERE last_modified_date >= unix_timestamp('2022-05-09 00:00:00', 'yyyy-MM-dd HH:mm:ss.SSS') * 1000
LIMIT 100;

如果确实需要先把字段转成标准日期格式再做比较，注意不能引用SELECT别名，要把完整的转换表达式写在WHERE子句中，同时时间值要加单引号。该写法因为需要逐行做类型转换，性能较差，仅适合数据量小的场景：

SELECT  
  last_modified_date, 
  from_timestamp(CAST(CAST(last_modified_date AS decimal(30,0))/1000 AS timestamp), "yyyy-MM-dd HH:mm:ss.SSS") AS "Completed_Date" 
FROM helix_access.chg_infrastructure_change 
WHERE from_timestamp(CAST(CAST(last_modified_date AS decimal(30,0))/1000 AS timestamp), "yyyy-MM-dd HH:mm:ss.SSS") >= '2022-05-09 00:00:00.000'
LIMIT 100;

• 数值型存储的时间戳字段，优先选直接数值比较的方案：除了性能更好，还能避免时区差异、时间格式解析规则不一致导致的匹配错误
• 只有筛选条件涉及复杂日期运算（比如按自然月、自然周截断匹配）时，再考虑转换为日期类型做筛选

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