Hive时间函数，

Hive时间函数，

天数

current_date 不是参数，只能取到当时真实的那天，所以如果要调度之前的日期，不可以用current_date，要把${date}转换成 ‘yyyy-MM-dd’ 的格式

${date}与current_date的转换

unix_timestamp中必须是字符串，或者date形式
Current_date = from_unixtime(unix_timestamp(’${date}’,‘yyyymmdd’),‘yyyy-mm-dd’)

• 本月第一天（current_date）

select date_sub(current_date,dayofmonth(current_date)-1)
2019-04-01

select from_unixtime(unix_timestamp(date_sub(current_date,dayofmonth(current_date)-1),‘yyyy-MM-dd’),‘yyyyMMdd’)
20190401

select date_format(current_date,‘yyyy-MM-01’)
2019-05-01

select from_unixtime(unix_timestamp(date_format(current_date,‘yyyy-MM-01’),‘yyyy-MM-dd’),‘yyyyMMdd’)
20190501

• 本月第一天（${date}）
  select from_unixtime(unix_timestamp(date_format(from_unixtime(unix_timestamp(‘20190228’,‘yyyymmdd’),‘yyyy-mm-dd’),‘yyyy-MM-01’),‘yyyy-MM-dd’),‘yyyyMMdd’)

• 本月第2天
  select date_sub(current_date,dayofmonth(current_date)-2)
  2019-04-02

• 本月最后一天
  last_day(string date),返回这个月的最后一天的日期
  select last_day(current_date)
  2019-04-30
  select from_unixtime(unix_timestamp(last_day(current_date),‘yyyy-MM-dd’),‘yyyyMMdd’)
  20190430

• 上个月
  select add_months(current_date, -1);
  2019-04-29

• 下个月
  select add_months(current_date, 1);
  2019-06-29

• 上月第一天
  select add_months(date_sub(current_date,dayofmonth(current_date)-1), -1);
  2019-04-01
  select from_unixtime(unix_timestamp(add_months(date_sub(current_date,dayofmonth(current_date)-1), -1),‘yyyy-MM-dd’),‘yyyyMMdd’)
  20190401

• 上月最后一天
  注意last_day函数里边要传入string类型,传参数${date}会得到null- 上月最后一天
  select date_sub(current_date,dayofmonth(current_date)+1)
  2019-03-30
  select from_unixtime(unix_timestamp( date_sub(current_date,dayofmonth(current_date)+1),‘yyyy-MM-dd’),‘yyyyMMdd’)

• 转型: 日期型转为整形,与原表中字段匹配
  select from_unixtime(unix_timestamp(date_sub(current_date,dayofmonth(current_date)+1),‘yyyy-MM-dd’),‘yyyyMMdd’)
  20190330

• 下个周一

next_day，返回当前时间的下一个星期几所对应的日期 。如下
select next_day(‘2018-02-27 10:03:01’, ‘TU’);
2018-03-06
说明，输入日期为2-27，下个星期的周二为03-06，如果想要知道下周一的日期就是MO，周日就是SU，以此类推。

月份

• 上个月的月份
  substr(add_months(from_unixtime(unix_timestamp(’${date}’,‘yyyyMMdd’),‘yyyy-MM-dd’),-1),6,2)
  这样就很复杂, 注意最后的substr是从6开始的,因为这种形式包含了’-’.
  03

• 上个月的月份
  select substr(date_sub(current_date,dayofmonth(‘2019-04-11’)+1),6,2);
  03

• Tips
  DATE_SUB() only takes STRING/TIMESTAMP/DATEWRITABLE types as first argument, got INT Query log
  date_sub()中的第一个参数可以写current_date(), 不可以传参${date}

毫秒转换

lastActiveTime是毫秒级别的

• floor(lastActiveTime/1000)
• cast(substr(firstactivetime,1,10) as int)

yyyyMMdd转换到yyyy-MM-dd

• 错误:

select from_unixtime(unix_timestamp(20190101,‘yyyymmdd’),‘yyyy-mm-dd’)
SemanticException [Error 10014]: Line 1:21 Wrong arguments ‘‘yyyymmdd’’: The function UNIX_TIMESTAMP takes only string/date/timestamp types Query log:

• 正确:

select from_unixtime(unix_timestamp(‘20190101’,‘yyyymmdd’),‘yyyy-mm-dd’)
2019-01-01

• 正确:

select from_unixtime(unix_timestamp(’${date}’,‘yyyymmdd’),‘yyyy-mm-dd’)
2019-05-29

日期计算

错误方式:
from_unixtime(cast(substr(firstactivetime,1,10) as int),‘yyyyMMdd’)+30
===> 2.0120652E7

加法的正确方式:date_add
date_add(from_unixtime(cast(substr(firstactivetime,1,10) as int),‘yyyy-MM-dd’),30)
2012-07-22

错误方式1:
DATEDIFF(day,a.firstactivetime,b.firstactivetime) — 报错, day is not right table alias

错误方式2:
DATEDIFF(a.firstactivetime,b.firstactivetime) — 结果都是null
错误原因: DATEDIFF函数里边要求是中划线的日期格式

减法正确方式:datediff
datediff(string enddate, string startdate)

DATEDIFF(from_unixtime(cast(substr(b.firstactivetime,1,10) as int),‘yyyy-MM-dd’),
from_unixtime(cast(substr(a.firstactivetime,1,10) as int),‘yyyy-MM-dd’)) as lifespan