hivedatediff函数(hive date_diff)
hive6:字符串和日期的转换常用函数
用到from_unixtime和unix_timestamp两种函数：
from_unixtime：时间戳转日期函数
用法：from_unixtime(bigint unixtime[, stringformat])
返回值: string
substr(from_unixtime(unix_timestamp()),1,10)
结果为：2017-01-03
select from_unixtime(unix_timestamp('20180905','yyyymmdd'),'yyyy-mm-dd')
from dw.ceshi_data
结果如下：
2018-09-05
2018-09-05转成20180905
select from_unixtime(unix_timestamp('2018-09-05','yyyy-mm-dd'),'yyyymmdd')
from dw.ceshi_data
结果如下：
20180905
用法:unix_timestamp(string date)
注意：里面格式必须是yyyy-MM-dd HH:mm:ss，如果不是会返回null值
返回值: bigint
from dw.ceshi_data;
结果如下：
1536120063
获取当前日期的时间戳：
select unix_timestamp()
from dw.ceshi_data;
结果如下：
1536126324
hive表中，存放着无法直接识别的字符串格式的时间，如'20170728102031',要计算两个时间相差的秒数。
1、先将字符串调整为hive可以识别的格式，即将形如'20170728102031' 转成 '2017-07-28 10:20:31'。 因为hive的 regexp_replace 不支持子语句，没法一次转换，只能用万能的 substr 和拼接函数来写了
select concat(substr('20170728102031',1,4),'-',
substr('20170728102031',5,2),'-',
substr('20170728102031',7,2),' ',
substr('20170728102031',9,2),':',
substr('20170728102031',11,2),':',
substr('20170728102031',13,2))
select unix_timestamp(concat(substr('20170728102031',1,4),'-',substr('20170728102031',5,2),'-',
substr('20170728102031',7,2),' ',
substr('20170728102031',9,2),':',
substr('20170728102031',11,2),':',
substr('20170728102031',13,2))) - unix_timestamp(concat(substr('20170728112031',1,4),'-',
substr('20170728112031',5,2),'-',
substr('20170728112031',7,2),' ',
substr('20170728112031',9,2),':',
substr('20170728112031',11,2),':',
substr('20170728112031',13,2)))
hive select create_time ,datediff(from_unixtime(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’), create_time) from test;
结果：当前时间是2017-11-16 与create_time的11-10之间差了6天，输出6；
hive select datediff(’2012-12-08′,’2012-05-09′) from dual;
213
日期时间转日期函数: to_date语法: to_date(string timestamp) 返回：string
hive select to_date(’2011-12-08 10:03:01′) from dual;
2011-12-08
2011
以下可以取到 month，hour，minute，second 用法和上面的一样
日期转周函数: weekofyear语法: weekofyear (string date) 返回值: int 说明: 返回日期在当前的周数。
hive select weekofyear(’2011-12-08 10:03:01′) from dual;
49
日期增加函数: date_add语法:
date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期
举例：
hive select date_add(’2012-12-08′,10) from dual;
2012-12-18
日期减少函数: date_sub语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例：
hive select date_sub(’2012-12-08′,10) from dual;
2012-11-28
所以我们利用其中的hour和datediff来获取create_time与当前时间的小时差：
hive select create_time,
(hour(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'))-
hour(create_time)+(datediff(from_unixtime(unix_timestamp(),
'yyyy-MM-dd HH:mm:ss'), create_time))*24) as hour_dValue
hive中如何求两个时间点之间相差月份数，我只知道datediff函数可以求天数
select floor((unix_timestamp(substr('201402',1,6),'yyyyMM')-unix_timestamp(substr('20141112',1,6),'yyyyMM'))/2629495);
解释：
格式：两个时间的格式自己随意指定
数字2629495解释。一年有365天4小时58分56秒。折算下秒数再除以12，得到2629495。
然后自己理解下这个数字就明白了。
Hive内置函数之时间函数
零、生产常用组合方式
（0.1）离线数仓获取昨天的日期作为分区，格式yyyyMMdd
regexp_replace(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) ,'-','')
或者
date_format(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),'yyyyMMdd')
一、源码部分
Hive的函数类为：org.apache.hadoop.hive.ql.exec.FunctionRegistry
二、常用时间函数
对于函数，除了知道怎么用，还需要知道返回值是什么类型，这里给出官方文档，文档中给出了函数的返回值类型
官方文档见：
（2.1）from_unixtime(bigint unixtime[, string format])
示例：
select from_unixtime(1591627588); --?2020-06-08 22:46:28
select from_unixtime(1591627588,'yyyyMMddHHmmss'); --?20200608224628
（2.2）unix_timestamp()、unix_timestamp(string date)、unix_timestamp(string date, string pattern)
示例：
select unix_timestamp('2020-06-08 22:50:00'); --?1591627800
select unix_timestamp('20200608225000','yyyyMMddHHmmss'); --?1591627800
（2.3）to_date(string timestamp)
示例：
SELECT to_date('2009-07-30 04:17:52'); --?2009-07-30
（2.4）year(string date)、month(string date)、day(string date)、hour(string date)、minute(string date)、second(string date)
这些函数是差不多的，都是从一个时间字符串中抽取出某个特定的时间字段。具有相同功能的还有extract(field FROM source)函数
示例：
SELECT day('2009-07-29 20:30:40'); -- 29
SELECT minute('2009-07-29 20:30:40'); -- 30
（2.5）date_add(date/timestamp/string?startdate, tinyint/smallint/int days)、date_sub(date/timestamp/string?startdate, tinyint/smallint/int days)
这两个功能是类似的
示例：
SELECT date_add('2009-07-30 20:50:59', 1); --?2009-07-31
（2.6）datediff(string enddate, string startdate)
截图中结果是错误的，应该为-1。
示例：
SELECT datediff('2009-06-30', '2009-07-02'); --?-2
SELECT datediff('2009-07-30', '2009-07-28'); -- 2
（2.7）current_date、current_timestamp
这两个函数使用desc function extended 查看会报错
示例：
（2.8）date_format(date/timestamp/string ts, string fmt)
示例：
SELECT date_format('2015-04-08', 'yyyyMMdd'); --?20150408
Hive中与时间相关的内置函数
（1）获取当前的时间戳（以秒位单位）：unix_timestamp()，返回值类型位BIGINT。例如：
select unix_timestamp() from dim_user limit 1; ? ? // 在MySQL, Oracle中会有一个虚拟表dual用来做一些通用的查询操作，但是hive中的表必须是注册的表，不存在dual表，所以必须只指定一个存在的表，例如已注册的dim_user表。
如果unix_timestamp()带一个string类型的date， 则会将date代表的时间转换为unix 时间。 例如：
select unix_timestamp('2013-01-13 13:09:10') from dim_user limit 1; ? // 注意date的格式必须为yyyy-MM-dd HH:mm:ss
如果unix_timestamp()的第二个参数指定了date format，则会根据该format转换成Unix时间戳。 例如：
select unix_timestamp('2013-01-13 13:09:10'，'yyyy-MM-dd') from dim_user limit 1; ?//会忽略后面的小时等信息
（2）将时间戳转换为指定格式的日期字符串： from_unixtime(BIGINT, 'format')，返回string类型，例如：
select from_unixtime(1476288000, 'yyyy-MM-dd') from dim_user limit 1;? ? // 返回2016-10-13
（3）日期增加函数：date_add(string startdate, interval), 返回string类型，例如：
select date_add('2016-10-12', 30) from dim_user limit 1;? ? ? // 返回2016-11-11
（4）日期减少函数：date_sub(string startdate, interval), 返回string类型，例如：
select date_sub('2016-10-12', 30) from dim_user limit 1;? ? ? // 返回2016-09-12
（5）日期间隔函数：datediff(string startdate, string endate),返回值为int类型， 例如：
select datediff('2016-10-19', '2016-09-20') from dim_user limit 1; // 返回29
（6）to_date(), year()，month(), day(), hour(), minute(), second() 分别获取日期字符串的日期，年份，月份，天份, 小时，分钟，秒数等，例如：
select to_date('2016-09-10 11:11:23') from dim_user limit 1;? // 返回2016-09-10