大数据学习（127）-hive日期函数

发布于：2025-06-07 ⋅ 阅读:(18) ⋅ 点赞:(0)

🍋🍋大数据学习🍋🍋

🔥系列专栏： 👑哲学语录: 用力所能及，改变世界。
💖如果觉得博主的文章还不错的话，请点赞👍+收藏⭐️+留言📝支持一下博主哦🤞

一、日期提取类函数

1. dayofweek(date)
  • 功能：返回日期的星期几（1 = 星期日，2 = 星期一，...，7 = 星期六）。
  • 示例： 
    SELECT dayofweek('2023-10-01');  -- 返回 1（星期日）
2. year(date) / month(date) / day(date)
  • 功能：提取日期的年、月、日部分。
  • 示例： 
    SELECT year('2023-10-01');  -- 返回 2023
SELECT month('2023-10-01');  -- 返回 10
SELECT day('2023-10-01');    -- 返回 1
3. hour(time) / minute(time) / second(time)
  • 功能：提取时间的时、分、秒部分。
  • 示例： 
    SELECT hour('2023-10-01 14:30:45');  -- 返回 14
SELECT minute('2023-10-01 14:30:45');  -- 返回 30
SELECT second('2023-10-01 14:30:45');  -- 返回 45
4. weekofyear(date)
  • 功能：返回日期在当年的周数（1-53）。
  • 示例： 
    SELECT weekofyear('2023-10-01');  -- 返回 40（第40周）
5. dayofmonth(date) / dayofyear(date)
  • 功能：返回日期在当月或当年的天数。
  • 示例： 
    SELECT dayofmonth('2023-10-01');  -- 返回 1
SELECT dayofyear('2023-10-01');   -- 返回 274（10月1日是第274天）

二、日期计算类函数

1. datediff(enddate, startdate)
  • 功能：计算两个日期之间的天数差（enddate - startdate）。
  • 示例： 
    SELECT datediff('2023-10-10', '2023-10-01');  -- 返回 9
2. date_add(startdate, days) / date_sub(startdate, days)
  • 功能：在日期上加减指定天数。
  • 示例： 
    SELECT date_add('2023-10-01', 7);  -- 返回 '2023-10-08'
SELECT date_sub('2023-10-01', 3);  -- 返回 '2023-09-28'
3. add_months(startdate, months)
  • 功能：在日期上加减指定月数。
  • 示例： 
    SELECT add_months('2023-10-01', 3);  -- 返回 '2024-01-01'
SELECT add_months('2023-10-31', 1);  -- 返回 '2023-11-30'（自动处理月底）
4. months_between(enddate, startdate)
  • 功能：计算两个日期之间的月数差（结果为浮点数）。
  • 示例： 
    SELECT months_between('2023-12-01', '2023-10-01');  -- 返回 2.0
SELECT months_between('2023-10-15', '2023-10-01');  -- 返回 ~0.5

三、日期格式化类函数

1. date_format(date, format)
  • 功能：将日期格式化为指定字符串（支持 yyyy-MM-dd 等格式）。
  • 示例： 
    SELECT date_format('2023-10-01', 'yyyy-MM-dd');  -- 返回 '2023-10-01'
SELECT date_format('2023-10-01', 'yyyy/MM/dd');  -- 返回 '2023/10/01'
SELECT date_format('2023-10-01', 'MM-dd-yyyy');  -- 返回 '10-01-2023'
2. from_unixtime(unix_timestamp [, format])
  • 功能：将 Unix 时间戳转换为日期字符串（默认格式 yyyy-MM-dd HH:mm:ss）。
  • 示例： 
    SELECT from_unixtime(1696185600);  -- 返回 '2023-10-01 00:00:00'
SELECT from_unixtime(1696185600, 'yyyy-MM-dd');  -- 返回 '2023-10-01'
3. unix_timestamp([date [, format]])
  • 功能：将日期字符串转换为 Unix 时间戳（秒）。
  • 示例： 
    SELECT unix_timestamp('2023-10-01 00:00:00');  -- 返回 1696185600
SELECT unix_timestamp('2023/10/01', 'yyyy/MM/dd');  -- 返回 1696185600

四、特殊日期函数

1. current_date
  • 功能：返回当前日期（格式 yyyy-MM-dd）。
  • 示例： 
    SELECT current_date;  -- 返回执行时的日期，如 '2023-10-01'
2. current_timestamp
  • 功能：返回当前时间戳（带时分秒）。
  • 示例： 
    SELECT current_timestamp;  -- 返回 '2023-10-01 14:30:45.123'
3. trunc(date, format)
  • 功能：将日期截断到指定单位（年、月等）。
  • 示例： 
    SELECT trunc('2023-10-15', 'YEAR');  -- 返回 '2023-01-01'（年初）
SELECT trunc('2023-10-15', 'MONTH');  -- 返回 '2023-10-01'（月初）
4. last_day(date)
  • 功能：返回日期所在月份的最后一天。
  • 示例： 
    SELECT last_day('2023-10-01');  -- 返回 '2023-10-31'
SELECT last_day('2023-02-01');  -- 返回 '2023-02-28'（平年2月）
5. next_day(date, weekday)
  • 功能：返回日期之后的第一个指定星期几（weekday 为英文缩写，如 'MO'、'TU'）。
  • 示例： 
    SELECT next_day('2023-10-01', 'MO');  -- 返回 '2023-10-02'（下一个周一）
SELECT next_day('2023-10-01', 'SU');  -- 返回 '2023-10-08'（下一个周日）

五、时区转换函数

1. from_utc_timestamp(timestamp, timezone)
  • 功能：将 UTC 时间转换为指定时区的时间。
  • 示例： 
    SELECT from_utc_timestamp('2023-10-01 00:00:00', 'Asia/Shanghai');  -- 返回 '2023-10-01 08:00:00'
2. to_utc_timestamp(timestamp, timezone)
  • 功能：将指定时区的时间转换为 UTC 时间。
  • 示例： 
    SELECT to_utc_timestamp('2023-10-01 08:00:00', 'Asia/Shanghai');  -- 返回 '2023-10-01 00:00:00'

注意事项

  1. 日期格式要求：

    • 大多数函数要求日期为 yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss 格式。
    • 若格式不符，需先用 unix_timestamp() 或 date_format() 转换。

  2. 版本差异：

    • Hive 2.x 及以上版本支持更多函数（如 date_format），低版本可能需使用 from_unixtime 替代。

  3. 性能建议：

    • 避免在 WHERE 子句中对日期字段使用函数（如 WHERE year(date_col) = 2023），会导致全表扫描。
    • 推荐用范围查询：WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01'。

示例场景

场景 1：计算用户注册后 7 天内的活跃率
WITH user_registration AS (
    SELECT 
        user_id,
        reg_date
    FROM users
),
user_activity AS (
    SELECT 
        user_id,
        activity_date
    FROM user_logs
)
SELECT 
    ur.user_id,
    ua.activity_date,
    DATEDIFF(ua.activity_date, ur.reg_date) AS days_since_reg,
    CASE WHEN DATEDIFF(ua.activity_date, ur.reg_date) <= 7 THEN 1 ELSE 0 END AS is_active_within_7d
FROM user_registration ur
LEFT JOIN user_activity ua ON ur.user_id = ua.user_id;
场景 2：统计每月最后一天的订单量
SELECT 
    order_date,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date = last_day(order_date)
GROUP BY order_date;

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布