HIVE SQL时间函数使用

时间戳是数据库常用的存放日期的形式之一，表示从 UTC 时间’1970-01-01 00:00:00’开始到现在的秒数，与常规时间格式如 ‘2018-01-01 00:00:00’可以相互转换，方法如下。

一、unix_timestamp 函数用法

1、unix_timestamp() 返回当前时间戳。另外，current_timestamp() 也有同样作用。

hive> select unix_timestamp();
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
1530241405
Time taken: 0.237 seconds, Fetched: 1 row(s)
1
2
3
4
5

2、unix_timestamp(string date) 返回 date 对应的时间戳，date 格式必须为 yyyy-MM-dd HH:mm:ss。

hive> select unix_timestamp('2018-06-29 00:00:00');
OK
1530201600
Time taken: 0.232 seconds, Fetched: 1 row(s)
1
2
3
4

3、unix_timestamp(string date, string format) 返回 date 对应的时间戳，date 格式由 format 指定。

hive> select unix_timestamp('2018/06/29 09', 'yyyy/MM/dd HH');
OK
1530234000
Time taken: 0.755 seconds, Fetched: 1 row(s)
1
2
3
4

4、unix_timestamp(string timestamp, string format) 返回 hive timestamp对应的unix时间戳，格式由 format 指定。

二、from_unixtime 函数用法

1、from_unixtime(int/bigint timestamp) 返回 timestamp 时间戳对应的日期，格式为 yyyy-MM-dd HH:mm:ss。

hive> select from_unixtime(1000000000);
OK
2001-09-09 09:46:40
Time taken: 0.316 seconds, Fetched: 1 row(s)
1
2
3
4

2、from_unixtime(int/bigint timestamp, string format) 返回 timestamp 时间戳对应的日期，格式由 format 指定。

hive> select from_unixtime(1000000000, 'yyyy/MM/dd HH');
OK
2001/09/09 09
Time taken: 0.24 seconds, Fetched: 1 row(s)
1
2
3
4

三、HIVE字段timestamp格式处理

如果要转换hive timestamp格式，可直接当成yyyy-MM-dd HH:mm:ss格式的字符串使用，如转换为yyyy-MM-dd格式，处理方式如下：

select from_unixtime(cast(a.t_insrnc_end_tm as int), 'yyyy-MM-dd');

select from_unixtime(unix_timestamp(a.t_insrnc_end_tm), 'yyyy-MM-dd') ;

select substr(a.t_insrnc_end_tm,1,10);

**获取yyy-MM-DD当前日期**

    SELECT CURRENT_DATE；

---	2019-06-25
 
**获取yyy-MM-DD hh:mm:ss当前日期**

     SELECT CURRENT_TIMESTAMP；

--	2019-06-25 15:33:25

**获取当前时间unix时间戳**

    SELECT unix_timestamp()

--1561447860

 **获取指定时间unix时间戳**

     SELECT unix_timestamp('20190625','yyyymmdd')

--1548345960

**将unix时间戳转换为指定格式时间**

    SELECT from_unixtime(unix_timestamp(),'yyyy-MM-dd')

--	2019-06-25

**将unix时间戳转换为默认格式时间**

    SELECT from_unixtime(unix_timestamp())

2019-06-25 15:33:25

**不同时间格式的转换**

    from_unixtime(unix_timestamp('20180905','yyyymmdd'),'yyyy-mm-dd')
    或者
    concat(substr('20180905',1,4),'-',substr('20180905',5,2),'-',substr('20180905',7,2))

**加减天数（yyyy-MM-dd）**

date_add(‘2008-12-31’, 1) = ‘2009-01-01’

date_sub(current_date ,1) = 昨天的日期(yyyy-MM-dd)

**获取前 n 个小时或者后 n 个小时的时间**

from_unixtime(unix_timestamp(date_format(current_timestamp,‘yyyy-MM-dd HH:mm:ss’) )-3600*n)

**获取日期中的年/月/日/时/分/秒/周**
dt=from_unixtime(unix_timestamp())

select year(dt),month(dt),day(dt),hour(dt),minute(dt),second(dt),weekofyear(dt)

**计算两个日期之间的天数: datediff**

select datediff(‘2017-09-15’,‘2017-09-01’) from dual;

**其它**

查询当月第几天: dayofmonth(current_date);
月末: last_day(current_date)
当月第1天: date_sub(current_date,dayofmonth(current_date)-1)
下个月第1天: add_months(date_sub(current_date,dayofmonth(current_date)-1),1)