Impala 日期时间函数详解

select version();

基于impala 3.2 版本
impalad version 3.2.0-cdh6.3.2 RELEASE (build 60867a3991e22a74e541ea7332eb9e7a34dca2a5)
Built on Wed Jan 29 22:38:21 PST 2020


时间的基本单元 timeunit

year 年
month 月
week  周
day   日
hour  小时
minute 分钟  
秒    second
毫秒  Millisecond （us）      0.0001 s  1/10^3 s
微妙  Microsecond （μs）    0.000001 s  1/10^6 s
纳秒  Nanosecond  （ns） 0.000000001 s  1/10^9 s  

此外还有一些时间单元
季度 quarter
十年  decade
世纪/百年 century
前年 millennium


纪元 EPOCH 
impala 采用的纪元和unix-like 系统的纪元一样是从1970-01-01 00：00:00 开始计时的。
这个是unix_time 相关的重要的概念。

日期时间格式函数中的同义函数：
1.now() ,current_timestamp
2.months_add = add_months
3.extract = data_trunc

1.基本时间单元的加减: timeunit_add|sub

impala中常见的时间单元有：year,month,week,day,hour,minute,second,millisecond,microsecond,nanosecond.

提供的函数明细如下：共10对20个函数

YEARS_ADD(TIMESTAMP date, INT years), YEARS_ADD(TIMESTAMP date, BIGINT years)
YEARS_SUB(TIMESTAMP date, INT years), YEARS_SUB(TIMESTAMP date, BIGINT years)
MONTHS_ADD(TIMESTAMP date, INT months), MONTHS_ADD(TIMESTAMP date, BIGINT months)
MONTHS_SUB(TIMESTAMP date, INT months), MONTHS_SUB(TIMESTAMP date, BIGINT months)
WEEKS_ADD(TIMESTAMP date, INT weeks), WEEKS_ADD(TIMESTAMP date, BIGINT weeks)
WEEKS_SUB(TIMESTAMP date, INT weeks), WEEKS_SUB(TIMESTAMP date, BIGINT weeks)
DAYS_ADD(TIMESTAMP startdate, INT days), DAYS_ADD(TIMESTAMP startdate, BIGINT days)
DAYS_SUB(TIMESTAMP startdate, INT days), DAYS_SUB(TIMESTAMP startdate, BIGINT days)
HOURS_ADD(TIMESTAMP date, INT hours), HOURS_ADD(TIMESTAMP date, BIGINT hours)
HOURS_SUB(TIMESTAMP date, INT hours), HOURS_SUB(TIMESTAMP date, BIGINT hours)
MINUTES_ADD(TIMESTAMP date, INT minutes), MINUTES_ADD(TIMESTAMP date, BIGINT minutes)
MINUTES_SUB(TIMESTAMP date, INT minutes), MINUTES_SUB(TIMESTAMP date, BIGINT minutes)
SECONDS_ADD(TIMESTAMP date, INT seconds), SECONDS_ADD(TIMESTAMP date, BIGINT seconds)
SECONDS_SUB(TIMESTAMP date, INT seconds), SECONDS_SUB(TIMESTAMP date, BIGINT seconds)
MICROSECONDS_ADD(TIMESTAMP date, INT microseconds), MICROSECONDS_ADD(TIMESTAMP date, BIGINT microseconds)
MICROSECONDS_SUB(TIMESTAMP date, INT microseconds), MICROSECONDS_SUB(TIMESTAMP date, BIGINT microseconds)
MILLISECONDS_ADD(TIMESTAMP date, INT milliseconds), MILLISECONDS_ADD(TIMESTAMP date, BIGINT milliseconds)
MILLISECONDS_SUB(TIMESTAMP date, INT milliseconds), MILLISECONDS_SUB(TIMESTAMP date, BIGINT milliseconds)
NANOSECONDS_ADD(TIMESTAMP date, INT nanoseconds), NANOSECONDS_ADD(TIMESTAMP date, BIGINT nanoseconds)
NANOSECONDS_SUB(TIMESTAMP date, INT nanoseconds), NANOSECONDS_SUB(TIMESTAMP date, BIGINT nanoseconds)


示例：
获取当前时间（精确到微妙）和纳秒

select now() as sysdate, nanoseconds_add(now(), 1) as sysdate_ns;
 sysdate	                 sysdate_ns
 2020-11-18 22:41:11.620787	 2020-11-18 22:41:11.620787001

2.timeunit 函数名称：

YEAR(TIMESTAMP date)    -- 获取年份
QUARTER(TIMESTAMP date) --获取季度
MONTH(TIMESTAMP date)   --获取月份
WEEK(TIMESTAMP date)    --获取一年的第几个周
DAY(TIMESTAMP date)    --获取当月的第几天
HOUR(TIMESTAMP date)   --获取小时数
MINUTE(TIMESTAMP date) --获取分钟
SECOND(TIMESTAMP date)  --获取小时
MILLISECOND(TIMESTAMP t) --获取微妙



示例：
select now() sysdate, year(now()) YYYY,quarter(now()) Q,month(now()) MM,week(now()) w,
day(now()) D,hour(now()) hh,minute(now()) mi,second(now()) s,MILLISECOND(now()) ms ;

sysdate	                     yyyy	q	mm	w	d	hh	mi	s	ms
2020-11-18 22:52:36.730434	 2020	 4	 11	 47	 18	 22	 52	 36	 730

3.获取timeunit的计数：

DAYOFYEAR(TIMESTAMP / DATE date)  --一年的第几天
DAYOFMONTH(TIMESTAMP / DATE date) ，DAY(TIMESTAMP / DATE date) --一个月的第几天
dayofweek(TIMESTAMP / DATE date) --一周的第几天
WEEKOFYEAR(TIMESTAMP date)     --一年的第几周
DAYNAME(TIMESTAMP / DATE date) --周几
MONTHNAME(TIMESTAMP date)      --月份的英文名


示例：

select now() sysdate,  dayofyear(now()) year_day,dayofmonth(now()) month_day,day(now()) month_day,dayofweek(now()) week_day, 
weekofyear(now()) year_week,  dayname(now()) weekday_name,monthname(now()) month_name;

sysdate	                  year_day	month_day	month_day	week_day	year_week	weekday_name	month_name
 2020-11-18 23:07:38.847301	 323	 18	         18	         4	        47	       Wednesday	 November

4.日期的加减：

ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days)
SUBDATE(TIMESTAMP startdate, INT days), SUBDATE(TIMESTAMP startdate, BIGINT days)
DATE_ADD(TIMESTAMP / DATE date, INT / BIGINT days), DATE_ADD(TIMESTAMP / DATE date, interval_expression)
DATE_SUB(TIMESTAMP / DATE date, INT / BIGINT days), DATE_SUB(TIMESTAMP / DATE date, interval_expression)

说明：
date_add|sub() 函数更佳通用一些，ADD|SUBDATE是为了兼容一些其他数据库中相同的名字。

示例：
select adddate(now(),3) 3days_later ,date_add(now(),3) 3days_later ,
date_add(now(),interval 3 days) 3days_later,date_add(now(),interval 3 weeks) 3weeks_later;

3days_later	                 3days_later	             3days_later	3weeks_later
2020-11-21 23:16:33.359172	 2020-11-21 23:16:33.359172	 2020-11-21 23:16:33.359172	 2020-12-09 23:16:33.359172

5.月份的加减：

ADD_MONTHS(TIMESTAMP/DATE date, INT/BIGINT months)
ADD_MONTHS() 和 MONTHS_ADD() 用法和参数一模一样

示例：
select  now() sysdate,add_months(now(),2  ) 2months_later,months_add(now(),2) 2months_later;

sysdate	2months_later	2months_later
 2020-11-18 23:19:35.510427	 2021-01-18 23:19:35.510427	 2021-01-18 23:19:35.510427

6.日期和时间戳的比对：

DATE_CMP(DATE date1, DATE date2)
TIMESTAMP_CMP(TIMESTAMP t1, TIMESTAMP t2)
上述两个函数比较日期和时间戳是否相等，返回-1,0,1三种数值。
DATEDIFF(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate) 
比较两个日期或者时间戳之间相差的天数。
INT_MONTHS_BETWEEN(TIMESTAMP newer, TIMESTAMP older) -- 返回两个时间戳相差的整数月份个数
MONTHS_BETWEEN(TIMESTAMP newer, TIMESTAMP older)     -- 返回浮点数的月数相差的数


示例：

select int_months_between('2015-03-31', '2015-01-20') month_gap, months_between(now() + interval 1 year, now()) months,months_between('2020-02-28','2020-03-01') months;

month_gap	months	months
 2	        12.0	 -0.12903225806451613

7.时间戳的转换：

TO_DATE(TIMESTAMP timestamp)
TO_TIMESTAMP(BIGINT unixtime), TO_TIMESTAMP(STRING date, STRING pattern)

FROM_UNIXTIME(BIGINT unixtime[, STRING format])
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(STRING datetime), UNIX_TIMESTAMP(STRING datetime, STRING format), UNIX_TIMESTAMP(TIMESTAMP datetime)

FROM_TIMESTAMP(TIMESTAMP datetime, STRING pattern), FROM_TIMESTAMP(STRING datetime, STRING pattern)
FROM_UTC_TIMESTAMP(TIMESTAMP timestamp, STRING timezone)
TO_UTC_TIMESTAMP(TIMESTAMP, STRING timezone)




示例：

select to_timestamp('1984/09/25', 'yyyy/MM/dd'),
  now() as 'current date/time',
  unix_timestamp(now()) 'now in seconds',
  to_timestamp(unix_timestamp(now()) - 60) as '60 seconds ago';

SELECT FROM_TIMESTAMP('1984-09-25 16:45:30.125', 'MMM dd, yyyy HH:mm:ss.SSS');

8.查询当前时间戳：

UTC_TIMESTAMP()
now()
current_timestamp()
TIMEOFDAY（）

 
示例：
select now() sysdate,current_timestamp() sysdate,UTC_TIMESTAMP(),TIMEOFDAY(),upper(timeofday()),regexp_replace(timeofday(), '.* ([A-Z]+)$', '\\1') as current_timezone;

sysdate	                     sysdate	utc_timestamp()	timeofday()	upper(timeofday())	current_timezone
2020-11-18 23:38:19.70778	 2020-11-18 23:38:19.70778	 2020-11-18 15:38:19.70778	 Wed Nov 18 23:38:19 2020 CST	 WED NOV 18 23:38:19 2020 CST	 CST

9.timeunit的提取：

EXTRACT(TIMESTAMP timestamp, STRING unit), EXTRACT(unit FROM TIMESTAMP ts)
DATE_PART(STRING part, TIMESTAMP / DATE date)=  EXTRACT()
DATE_TRUNC(STRING unit, TIMESTAMP / DATE ts)
TRUNC(TIMESTAMP timestamp, STRING unit)

10.其他函数：

LAST_DAY(TIMESTAMP t)  -- 获取指定月份的最后一天
NEXT_DAY(TIMESTAMP date, STRING weekday) --获取指定日期的下个周几
"Sunday"/"Sun", "Monday"/"Mon", "Tuesday"/"Tue", "Wednesday"/"Wed", "Thursday"/"Thu", "Friday"/"Fri", "Saturday"/"Sat"


示例：
select  now() sysdate,last_day(now()) current_month_lastday, last_day(now()) + interval 1 day next_month_firstday ,next_day(now(), 'Thu') next_Thursday;

sysdate	current_month_lastday	next_month_firstday	next_thursday
 2020-11-18 23:42:30.741747	 2020-11-30 00:00:00.0	 2020-12-01 00:00:00.0	 2020-11-19 23:42:30.741747

11.新增的功能和函数：

CURRENT_DATE

--时间戳的强制转换：
select CAST('2019.10.10 13:30:40.123456 +01:30'
   AS TIMESTAMP   FORMAT 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM';



select DATE'2013-01-01';

12.缺少的日期时间函数：

impala缺少timestamp_diff 函数需要借助unix_timestamp()函数进行相减

参考：

http://impala.apache.org/docs/build/html/topics/impala_datetime_functions.html