1select 2 day -- 时间 3 ,date_add(day,1 - dayofweek(day)) as week_first_day -- 本周第一天_周日 4 ,date_add(day,7 - dayofweek(day)) as week_last_day -- 本周最后一天_周六 5 ,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_first_day -- 本周第一天_周一 6 ,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_last_day -- 本周最后一天_周日 7 ,next_day(day,'TU') as next_tuesday -- 当前日期的下个周二 8 ,trunc(day,'MM') as month_first_day -- 当月第一天 9 ,last_day(day) as month_last_day -- 当月最后一天 10 ,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01')) as season_first_day -- 当季第一天 11 ,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_last_day -- 当季最后一天 12 ,trunc(day,'YY') as year_first_day -- 当年第一天 13 ,last_day(add_months(trunc(day,'YY'),12)) as year_last_day -- 当年最后一天 14 ,weekofyear(day) as weekofyear -- 当年第几周 15 ,second(day) as second -- 秒钟 16 ,minute(day) as minute -- 分钟 17 ,hour(day) as hour -- 小时 18 ,day(day) as day -- 日期 19 ,month(day) as month -- 月份 20 ,lpad(ceil(month(day)/3),2,0) as season -- 季度 21 ,year(day) as year -- 年份 22from ( 23 select '2018-01-02 01:01:01' as day union all 24 select '2018-02-02 02:03:04' as day union all 25 select '2018-03-02 03:05:07' as day union all 26 select '2018-04-02 04:07:10' as day union all 27 select '2018-05-02 05:09:13' as day union all 28 select '2018-06-02 06:11:16' as day union all 29 select '2018-07-02 07:13:19' as day union all 30 select '2018-08-02 08:15:22' as day union all 31 select '2018-09-02 09:17:25' as day union all 32 select '2018-10-02 10:19:28' as day union all 33 select '2018-11-02 11:21:31' as day union all 34 select '2018-12-02 12:23:34' as day 35) t1 36;
获取当前时间截:
11 select unix_timestamp() ; 22 +-------------+--+ 33 | _c0 | 44 +-------------+--+ 55 | 1521684090 | 66 +-------------+--+
获取当前时间1：
11 select current_timestamp; 22 +--------------------------+--+ 33 | _c0 | 44 +--------------------------+--+ 55 | 2018-03-22 10:04:02.568 | 66 +--------------------------+--+
获取当前时间2：
11 SELECT from_unixtime(unix_timestamp()); 22 +----------------------+--+ 33 | _c0 | 44 +----------------------+--+ 55 | 2018-03-22 10:04:38 | 66 +----------------------+--+
获取当前日期：
11 SELECT CURRENT_DATE; 22 +-------------+--+ 33 | _c0 | 44 +-------------+--+ 55 | 2018-03-22 | 66 +-------------+--+
**日期差值：**datadiff(结束日期，开始日期)，返回结束日期减去开始日期的天数。
11 select datediff(CURRENT_DATE,'2017-01-01') as datediff; 22 +-----------+--+ 33 | datediff | 44 +-----------+--+ 55 | 445 | 66 +-----------+--+
日期加减：date_add（时间，增加天数），返回值为时间天+增加天的日期；date_sub（时间，减少天数），返回日期减少天后的日期。
11 select date_add(current_date,365) as dateadd; 22 +-------------+--+ 33 | dateadd | 44 +-------------+--+ 55 | 2019-03-22 | 66 +-------------+--+
时间差：两个日期之间的小时差
11 select (hour('2018-02-27 10:00:00')-hour('2018-02-25 12:00:00')+(datediff('2018-02-27 10:00:00','2018-02-25 12:00:00'))*24) as hour_subValue; 22 +----------------+--+ 33 | hour_subValue | 44 +----------------+--+ 55 | 46 | 66 +----------------+--+
获取年、月、日、小时、分钟、秒、当年第几周
1 1 select 2 2 year('2018-02-27 10:00:00') as year 3 3 ,month('2018-02-27 10:00:00') as month 4 4 ,day('2018-02-27 10:00:00') as day 5 5 ,hour('2018-02-27 10:00:00') as hour 6 6 ,minute('2018-02-27 10:00:00') as minute 7 7 ,second('2018-02-27 10:00:00') as second 8 8 ,weekofyear('2018-02-27 10:00:00') as weekofyear 9 9 ; 1010 +-------+--------+------+-------+---------+---------+-------------+--+ 1111 | year | month | day | hour | minute | second | weekofyear | 1212 +-------+--------+------+-------+---------+---------+-------------+--+ 1313 | 2018 | 2 | 27 | 10 | 0 | 0 | 9 | 1414 +-------+--------+------+-------+---------+---------+-------------+--+
转成日期：
11 select to_date('2018-02-27 10:03:01') ; 22 +-------------+--+ 33 | _c0 | 44 +-------------+--+ 55 | 2018-02-27 | 66 +-------------+--+
当月最后一天：
11 select last_day('2018-02-27 10:03:01'); 22 +-------------+--+ 33 | _c0 | 44 +-------------+--+ 55 | 2018-02-28 | 66 +-------------+--+
当月第一天：
11 select trunc(current_date,'MM') as day; 22 +-------------+--+ 33 | day | 44 +-------------+--+ 55 | 2018-03-01 | 66 +-------------+--+
当年第一天：
11 select trunc(current_date,'YY') as day; 22 +-------------+--+ 33 | day | 44 +-------------+--+ 55 | 2018-01-01 | 66 +-------------+--+
next_day，返回当前时间的下一个星期几所对应的日期
11 select next_day('2018-02-27 10:03:01', 'TU'); 22 +-------------+--+ 33 | _c0 | 44 +-------------+--+ 55 | 2018-03-06 | 66 +-------------+--+ 7 8-- hive中怎么获取两个日期相减后的小时（精确到两位小数点），而且这两个日期有可能会出现一个日期有时分秒，一个日期没有时分秒的情况 9select 10 t3.day1 11 ,t3.day2 12 ,t3.day -- 日期 13 ,t3.hour -- 小时 14 ,t3.min -- 分钟 15 ,t3.day + t3.hour as hour_diff_1 16 ,t3.day + t3.hour + t3.min as hour_diff_2 17 ,round((cast(cast(t3.day1 as timestamp) as bigint) - cast(cast(t3.day2 as timestamp) as bigint)) / 3600,2) as hour_diff_3 -- 最优 18 ,(datediff(t3.day1,t3.day2) * 24) + (nvl(hour(t3.day1),0) - nvl(hour(t3.day2),0)) + round((nvl(minute(t3.day1),0) - nvl(minute(t3.day2),0)) / 60,2) as hour_diff_4 19from ( 20 select 21 t2.day1 22 ,t2.day2 23 ,(datediff(t2.day1,t2.day2) * 24) as day -- 日期 24 ,(hour(t2.day1) - hour(t2.day2)) as hour -- 小时 25 ,round((minute(t2.day1) - minute(t2.day2)) / 60,2) as min -- 分钟 26 from ( 27 select 28 cast(t1.day1 as timestamp) as day1 29 ,cast(t1.day2 as timestamp) as day2 30 from ( 31 select '2018-01-03 02:30:00' as day1, '2018-01-02 23:00:00' as day2 union all 32 select '2018-06-02 08:15:22' as day1, '2018-06-02 06:11:16' as day2 union all 33 select '2018-07-04' as day1, '2018-07-02 01:01:01' as day2 34 ) t1 35 ) t2 36) t3 37; 38 39+------------------------+------------------------+------+-------+--------+--------------+--------------+--------------+--------------+--+ 40| day1 | day2 | day | hour | min | hour_diff_1 | hour_diff_2 | hour_diff_3 | hour_diff_4 | 41+------------------------+------------------------+------+-------+--------+--------------+--------------+--------------+--------------+--+ 42| 2018-07-04 00:00:00.0 | 2018-07-02 01:01:01.0 | 48 | -1 | -0.02 | 47 | 46.98 | 46.98 | 46.98 | 43| 2018-01-03 02:30:00.0 | 2018-01-02 23:00:00.0 | 24 | -21 | 0.5 | 3 | 3.5 | 3.5 | 3.5 | 44| 2018-06-02 08:15:22.0 | 2018-06-02 06:11:16.0 | 0 | 2 | 0.07 | 2 | 2.07 | 2.07 | 2.07 | 45+------------------------+------------------------+------+-------+--------+--------------+--------------+--------------+--------------+--+ 46 47### 当周第一天，最后一天 48date -d "2018-10-24 $(($(date -d 2018-10-24 +%u)-1)) days ago" +%Y-%m-%d 49date -d "2018-10-24 $((7-$(date -d 2018-10-24 +%u))) days" +%Y-%m-%d