Hive日期处理

日期的各种转换和处理, 其中不包含DATE_FORMAT.

SELECT
    day, -- 时间
    DATE_ADD(day, 1 - DAYOFWEEK(day)) AS week_first_day, -- 本周第一天_周日
    DATE_ADD(day, 7 - DAYOFWEEK(day)) AS week_last_day, -- 本周最后一天_周六
    DATE_ADD(day, 1 - CASE WHEN DAYOFWEEK(day) = 1 THEN 7 ELSE DAYOFWEEK(day) - 1 END) AS week_first_day, -- 本周第一天_周一
    DATE_ADD(day, 7 - CASE WHEN DAYOFWEEK(day) = 1 THEN 7 ELSE DAYOFWEEK(day) - 1 END) AS week_last_day, -- 本周最后一天_周日
    NEXT_DAY(day,'TU') AS next_tuesday, -- 当前日期的下个周二
    TRUNC(day, 'MM') AS month_first_day,  -- 当月第一天
    LAST_DAY(day) AS month_lASt_day, -- 当月最后一天
    TO_DATE(CONCAT(YEAR(day), '-', LPAD(CEIL(month(day) / 3) * 3 - 2, 2, 0), '-01')) AS season_first_day, -- 当季第一天
    LAST_DAY(TO_DATE(CONCAT(YEAR(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) AS season_lASt_day, -- 当季最后一天
    TRUNC(day, 'YY') AS year_first_day, -- 当年第一天
    LAST_DAY(ADD_MONTHS(TRUNC(day, 'YY'), 11)) AS year_last_day, -- 当年最后一天
    WEEKOFYEAR(day) AS weekofyear, -- 当年第几周
    SECOND(day) AS second, -- 秒钟
    MINUTE(day) AS minute, -- 分钟
    HOUR(day) AS hour, -- 小时
    DAY(day) AS day, -- 日期
    MONTH(day) AS month, -- 月份
    LPAD(CEIL(MONTH(day)/3),2,0) AS season, -- 季度
    YEAR(day) AS year -- 年份
FROM (
    SELECT '2019-01-02 01:01:01' AS day UNION ALL 
    SELECT '2019-02-02 02:03:04' AS day UNION ALL 
    SELECT '2019-03-02 03:05:07' AS day UNION ALL 
    SELECT '2019-04-02 04:07:10' AS day UNION ALL 
    SELECT '2019-05-02 05:09:13' AS day UNION ALL 
    SELECT '2019-06-02 06:11:16' AS day UNION ALL 
    SELECT '2019-07-02 07:13:19' AS day UNION ALL 
    SELECT '2019-08-02 08:15:22' AS day UNION ALL 
    SELECT '2019-09-02 09:17:25' AS day UNION ALL 
    SELECT '2019-10-02 10:19:28' AS day UNION ALL 
    SELECT '2019-11-02 11:21:31' AS day UNION ALL 
    SELECT '2019-12-02 12:23:34' AS day
) detail