Hive中将'29-MAR-17'格式字符串转为日期类型的问题求助

嘿，我来帮你搞定这个Hive日期处理的问题！你遇到的NULL返回和范围查询失效，核心原因是日期格式匹配错误，咱们一步步来解决：

1. 为什么之前的语句返回NULL？ #

你用了UNIX_TIMESTAMP('open_time', 'dd/MM/yyyy')，这里有两个关键问题：

• 第一个参数应该是列名open_time，而不是加引号的固定字符串'open_time'（加引号后Hive会把它当成普通文本解析，自然匹配失败）
• 格式符dd/MM/yyyy和你的日期格式29-MAR-17完全不匹配，正确的格式符应该是dd-MMM-yy：
  • dd：两位日期数字
  • MMM：月份的英文缩写（比如MAR）
  • yy：两位年份数字

2. 正确的字符串转日期语句 #

用以下语句可以把open_time字符串转成标准日期类型：

SELECT TO_DATE(from_unixtime(UNIX_TIMESTAMP(open_time, 'dd-MMM-yy'))) AS open_time_date
FROM table_hive;

如果你的Hive版本是2.1.0及以上，还可以用更简洁的date_parse函数：

SELECT date_parse(open_time, '%d-%b-%y') AS open_time_date
FROM table_hive;

3. 实现日期范围查询 #

临时查询（无需修改表结构） #

直接在WHERE子句中转换后比较，注意BETWEEN的前后顺序要正确（小日期在前，大日期在后），否则会返回空结果：

SELECT * 
FROM table_hive 
WHERE date_parse(open_time, '%d-%b-%y') 
      BETWEEN date_parse('28-MAR-17', '%d-%b-%y') 
      AND date_parse('29-MAR-17', '%d-%b-%y');

（如果你的Hive版本较低，就把date_parse换成前面的TO_DATE(from_unixtime(UNIX_TIMESTAMP(...)))组合即可）

长期方案（修改表结构，提升查询效率） #

如果需要频繁做日期查询，建议直接把列类型改成DATE，避免每次查询都重复转换：

-- 方法1：创建新表，转换后存储数据
CREATE TABLE table_hive_date AS
SELECT 
    -- 保留其他所有列，替换open_time的类型
    *,
    date_parse(open_time, '%d-%b-%y') AS open_time_new
FROM table_hive;

-- 方法2：直接修改原表的列类型（前提是所有数据都能正确转换）
ALTER TABLE table_hive 
CHANGE COLUMN open_time open_time DATE 
COMMENT '转换后的日期类型列';

小提醒 #

如果你的数据中月份缩写有大小写混合（比如mar/MAR），可以用upper()或lower()统一格式，避免解析失败：

SELECT date_parse(upper(open_time), '%d-%b-%y') AS open_time_date
FROM table_hive;

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