Impala 日期时间函数详解 |
您所在的位置：网站首页 › 个人成长怎么填写才正确 › 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.6207870012.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 7303.获取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 November4.日期的加减： 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.3591725.月份的加减： 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.5104276.日期和时间戳的比对： 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.129032258064516137.时间戳的转换： 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 CST9.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.74174711.新增的功能和函数： 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 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |