Databricks社区版SQL：字符串转时间戳及时间差计算问题

核心问题原因 #

你遇到的NULL值问题，是因为to_timestamp()/to_date()默认使用的日期格式和你的输入字符串不匹配，导致解析失败返回NULL。必须指定完全匹配的自定义格式字符串才能正确转换。

具体解决步骤 #

1. 正确转换字符串为日期/时间戳类型 #

针对你的两种日期格式：

• 对于11/22/2012 03:00:45 PM这种带12小时制+AM/PM的格式，使用格式字符串'MM/dd/yyyy hh:mm:ss a'
• 对于11/22/2012这种纯日期格式，使用格式字符串'MM/dd/yyyy'

2. 计算日期/时间差值 #

转换完成后，就可以用datediff（计算天数差）或timestampdiff（指定单位计算差值）函数：

• datediff(end_ts, start_date)：返回结束时间与开始日期的天数差
• timestampdiff(unit, start, end)：支持指定HOUR/MINUTE/SECOND等单位

3. 提取年份 #

使用year()函数直接从转换后的日期/时间戳类型中提取年份。

完整示例SQL #

假设你的表名为your_table，包含col1和col2列，查询示例如下：

SELECT
  col1,
  col2,
  -- 转换为标准时间戳/日期类型
  to_timestamp(col1, 'MM/dd/yyyy hh:mm:ss a') AS col1_timestamp,
  to_date(col2, 'MM/dd/yyyy') AS col2_date,
  -- 计算天数差值
  datediff(col1_timestamp, col2_date) AS day_difference,
  -- 计算小时差值
  timestampdiff(HOUR, col2_date, col1_timestamp) AS hour_difference,
  -- 提取年份
  year(col1_timestamp) AS col1_year,
  year(col2_date) AS col2_year
FROM your_table;

关键注意事项 #

• 格式字符串的大小写严格对应：MM代表两位数月份，dd是两位数日期，yyyy是四位年份，hh是12小时制小时，a匹配AM/PM标记
• 如果需要将col2也转为时间戳，可使用to_timestamp(col2, 'MM/dd/yyyy')，默认会转为当天的00:00:00时间戳
• 确保格式字符串和输入字符串的结构完全一致，比如如果你的日期存在单数字的月份/日期（如1/2/2012），需要将格式字符串改为'M/d/yyyy'

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