Hive QL中timestamp与字符串比较及1970时间占位值过滤问题

现有写法存在的问题 #

• 格式匹配风险：数据集里的占位符是斜杠分隔的'1970/01/01 00:00:00'，而查询条件里写的是横杠分隔的'1970-01-01 00:00:00'，部分数据库做字符串到timestamp的隐式转换时可能无法识别格式，导致占位符匹配失败，应该被过滤的无效数据残留。
• 真实NULL遗漏：SQL遵循三值逻辑，NULL和任意值做=/<>比较的结果都是UNKNOWN，不会被WHERE条件命中。如果该列存在真正的NULL值，这些数据会被现有写法排除在结果之外，如果你预期保留真实NULL就会不符合需求。
• 时区适配问题：如果你的数据库timestamp类型带时区属性，写死的字符串时间会按数据库实例时区做转换，很可能和存储的UTC标准1970-01-01零点不匹配，比如东八区实例下你写的'1970-01-01 00:00:00'会被转成UTC时间1969-12-31 16:00:00，完全匹配不到占位符。

正确过滤逻辑 #

优先用时间戳数值比较，避免格式、时区问题：

场景1：仅过滤占位符，保留真实NULL #

-- 通用逻辑，UNIX时间戳0固定对应1970-01-01 00:00:00 UTC
SELECT * FROM table WHERE UNIX_TIMESTAMP(timestamp_col) <> 0

如果你的数据库没有UNIX_TIMESTAMP函数，可以用指定格式的日期转换函数避免格式问题：

-- MySQL 示例
SELECT * FROM table WHERE timestamp_col <> STR_TO_DATE('1970/01/01 00:00:00', '%Y/%m/%d %H:%i:%s')
-- PostgreSQL 示例
SELECT * FROM table WHERE timestamp_col <> TO_TIMESTAMP('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')

场景2：同时过滤占位符和真实NULL #

补充IS NOT NULL判断即可：

SELECT * FROM table 
WHERE timestamp_col IS NOT NULL 
AND UNIX_TIMESTAMP(timestamp_col) <> 0

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