hive笔记:时间函数的使用+格式的转换

一、时间函数的使用

1.from_unixtime(bigint unixtime[, string format]):将是将戳转化为日期

将时间的秒值转换成format格式(format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等)如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12

(1)时间戳为13位的情况:

问题:
其中第一个参数为bigint型数据,一般是10位的,遇到13位的时间戳,需要去掉最后三位才行,但是bigint型数据不支持直接算数运算,也不支持字符串截取
如,13位时间戳直接转换
(1)10位的时间戳:
        select from_unixtime(1445391280,'yyyy-MM-dd HH:mm:ss');
结果:2015-10-21 09:34:40
(2)13位的时间戳:
        select from_unixtime(1445391280000,'yyyy-MM-dd HH:mm:ss');
结果:47772-08-17 01:46:40
两种方法处理此问题:
a.一种是将bigint型数据先转成double型计算之后再转成bigint型,
   hive> select from_unixtime(cast(cast(1445391280000 as double)/1000 as bigint),'yyyy-MM-dd HH:mm:ss');
   2015-10-21 09:34:40
b.另一种是将bigint型数据转成string型,截取之后再转回bigint型。
   hive> select from_unixtime(cast(substr(cast(1445391280 as string),1,10) as bigint),'yyyy-MM-dd HH:mm:ss');
   2015-10-21 09:34:40

 案例:时间戳为13位的情况

 %jdbc(hive)
     select from_unixtime(cast(cast(pc.ttl as bigint) / 1000 as bigint), 'yy-mm-dd') as dt1,
     from_unixtime(cast(substr(pc.ttl,0,10) as int),'yy-MM-dd HH:mm:ss')  as dt2,
     from_unixtime(cast(cast(pc.ttl as bigint) / 1000 as bigint), 'yyyy-mm-dd') as dt3,
     from_unixtime(cast(substr(pc.ttl,0,10) as int),'yyyy-MM-dd HH:mm:ss')  as dt4
     from xxxx  pc

2.unix_timestamp获取当前UNIX时间戳函数:(将日期转化为时间戳)

(1) unix_timestamp()

返回值:   bigint
说明: 获得当前时区的UNIX时间戳

(2) unix_timestamp(string date)

返回值:   bigint
说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败,则返回0。

(3)unix_timestamp(string date, string pattern)

返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败,则返回0。

(4)案例如下(yy-MM-dd和yyMMdd两种时间格式):最好使用unix_timestamp(string date, string pattern)转化,表明时间格式

       a.yyMMdd

%jdbc(hive)  
select  a.dt as time,unix_timestamp(a.dt) as time1,
           unix_timestamp(a.dt,'yyMMdd') as time2,
           concat('20',a.dt) as dt0,
           unix_timestamp(concat('20',a.dt)) as dt1,
           unix_timestamp(concat('20',a.dt),'yyyyMMdd') as dt2
from track.click a
where concat('20',a.dt)>='20181101' and concat('20',a.dt)<='20181103'
limit 100

     b.yyyy-MM-dd

select created_at,
     unix_timestamp(created_at) created_at1,
     substr(created_at,1,10)as dt0,---结果为时间戳
     unix_timestamp(substr(created_at,1,10))dt1,---结果为空
     unix_timestamp(substr(created_at,1,10),'yyyy-MM-dd') dt2---结果时间戳
from trial_sdk.device
where created_at>='2018-11-01' and created_at<='2018-11-03'
limit 100

3. 获取年、月、日、天、小时、分钟、秒、周数、星期几、季节函数

select created_at,year(created_at),  --年

month(created_at),--

day(created_at),--

hour(created_at),--小时

minute(created_at),--分钟

second(created_at),--

weekofyear(created_at),--第几周

dayofweek(created_at),--星期几

ceil(month(created_at)/3)--季节

from trial_sdk.device

where to_date(created_at)='2019-08-27'

4. 日期计算函数

日期(月份)增减函数

 (1) 日期往后推几天,使用date_add函数,日期往前推几天,使用date_sub函数;

select created_at,date_add(created_at,10) daslater,date_sub(created_at,10) daysoff 

from trial_sdk.device

where to_date(created_at)='2019-08-27'

(2)月份往后推几个月,使用add_months函数,参数为正数;月份往前推几个月,参数为负数

select created_at,add_months(created_at,3) monthslater,

                            add_months(created_at,-3) monthsoff 

from trial_sdk.device

where to_date(created_at)='2019-08-27'

5. 两个日期(月份)相减函数

 (1)日期相减使用date_diff函数,返回相差天数;月份相减使用months_between,返回相差月份,不足月为小数

    select datediff('2019-08-23', '2019-08-21'),

       months_between('2019-08-21', '2019-07-25')

结果:2  0.87096774

(2)若计算两个之间相差的小时数,可以使用:

select hour('2019-08-23 10:11:12')-hour('2019-08-21 12:20:20') 

          +(datediff('2019-08-23 10:11:12','2019-08-21 12:20:20'))*24

结果:46

6. 获取月初(月末)、年初(年末)日期

  • 获取月末日期使用last_day函数,
  • 获取月初日期使用trunc(,'MM')函数;
  • 获取年初日期使用trunc(,‘YY’)函数,使用date_sub(add_months(trunc('',

select last_day(created_at),  --月末

trunc(created_at, 'MM'),  --月初

trunc(created_at, 'YY')--年初

date_sub(add_months(trunc(created_at, 'YY'),12),1) --年末

from trial_sdk.device

where to_date(created_at)='2019-08-27'

 

7. 获取日期的下个星期几的日期

 使用函数next_day获取日期下个星期几的日期,

 参数: 周一:MO;周二:TU;周三:WE ;周四:TH ;周五:FR ;周六:SA;周日SU

select next_day(created_at, 'MO'),---下个星期一的日期:9.2

          next_day(created_at, 'TU'),---下个星期二的日期:9.3

          next_day(created_at, 'WE'),---下个星期三的日期

    next_day(created_at, 'TH'),---下个星期四的日期

           next_day(created_at, 'FR'),---下个星期五的日期

    next_day(created_at, 'SA'),---下个星期六的日期

          next_day(created_at, 'SU') ---下个星期日的日期

from trial_sdk.device

where to_date(created_at)='2019-08-27' 

二、时间格式的转换

 

posted @ 2018-11-29 18:17  未来0087  阅读(...)  评论(...编辑  收藏