Impala SQL中如何从datetime字段提取HH:MM格式的时分？

你可以通过concat()结合lpad()函数，把提取出的时、分拼接成HH:MM格式，解决单个数字时/分的补零问题。完整SQL语句如下：

SELECT
  year(cast(dt_tm_entry as timestamp)) AS entry_year,
  month(cast(dt_tm_entry as timestamp)) AS entry_month,
  day(cast(dt_tm_entry as timestamp)) AS entry_day,
  concat(
    lpad(hour(cast(dt_tm_entry as timestamp)), 2, '0'),
    ':',
    lpad(minute(cast(dt_tm_entry as timestamp)), 2, '0')
  ) AS entry_time
FROM your_table_name;

关键函数说明： #

• lpad(num, 2, '0')：将提取出的时/分（数字类型）转为两位字符串，不足两位时在左侧补零，比如把9转为09，12保持12
• concat(a, ':', b)：把补零后的时、分用冒号连接，最终得到HH:MM格式的时间字符串

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