Impala 日期操作相关

获取指定日期的周初和周末:

select date_sub(now(),dayofweek(now())-1-1) weekstart,date_sub(now(),dayofweek(now())-1-7) weekend;


-- 时间戳取整
Impala 2.11 之前的取整当前时间的写法:
select trunc(now(), 'YEAR') --取整到年份, 得到当年 1 月 1 日 0 点 0 分
select trunc(now(), 'MONTH') --取整到月份, 得到当月 1 日 0 点 0 分
select trunc(now(), 'DD') --取整到日期, 得到当天 0 点 0 分
select trunc(now(), 'DAY') --取整到星期, 得到本星期第一天的 0 点 0 分
select trunc(now(), 'HH24') --取整到小时, 得到当前小时的 0 分
select trunc(now(), 'MI') --取整到分钟, 得到当前分钟 0 秒


Impala 2.11 之后增加了 date_trunc() 函数, 下面是几个取整的写法:
date_trunc('year',now())
date_trunc('month',now())
date_trunc('week',now())
date_trunc('day',now())
date_trunc('hour',now())
date_trunc('minute',now())
date_trunc() 的语法和 date_part() 类似, 下面是完整的时间 part 列表:
microseconds
milliseconds
second
minute
hour
day
week
month
year
decade
century
millennium

日期格式转换:
 

with dt as (
select '7/15/2020' as dt
union all select '7/1/2020'
union all select '12/31/2020'
union all select '1/1/2020'
)
select  dt, concat(split_part(dt,"/",3),"-",case when length(split_part(dt,"/",1))=1 then lpad(split_part(dt,"/",1),2,"0") else split_part(dt,"/",1) end,"-"
                                               ,case when length(split_part(dt,"/",2))=1 then lpad(split_part(dt,"/",2),2,"0") else split_part(dt,"/",2) end ) "YYYY-MM-DD"
                                               from dt;

序号	dt	     yyyy-mm-dd
 1	 7/15/2020	 2020-07-15
 2	 7/1/2020	 2020-07-01
 3	 12/31/2020	 2020-12-31
 4	 1/1/2020	 2020-01-01


--impala获取当前日期(方法1)
select substr(regexp_replace(cast(now() as string),'-',''),1,8) strdate
20201110

--impala获取当前日期(方法2)
 select concat(
    cast(year(now()) as string),
    lpad(cast(month(now()) as string),2,'0'),
    lpad(cast(day(now()) as string),2,'0')
) str_date

20201110


 select now() currenttime, from_unixtime(unix_timestamp( now() ),'yyyyMMddHHmmssSSSSS') str_time 

 currenttime	str_time
 1	 2020-11-10 16:04:01.347408	 2020111016040100000
 with dt as (
select '7/15/2020' as dt
union all select '7/1/2020'
union all select '12/31/2020'
union all select '1/1/2020'
)
select  dt,to_timestamp(dt,'M/d/yyyy') data_dt,strleft(cast(to_timestamp(dt,'M/d/yyyy') as string), 10) dts from dt


     dt	         data_dt	             dts
 1	 7/15/2020	 2020-07-15 00:00:00.0	 2020-07-15
 2	 7/1/2020	 2020-07-01 00:00:00.0	 2020-07-01
 3	 12/31/2020	 2020-12-31 00:00:00.0	 2020-12-31
 4	 1/1/2020	 2020-01-01 00:00:00.0	 2020-01-01
select now(), replace(regexp_replace(cast(now() as string),'[:digit:]',''),'-','') str_time


now()	str_time
  2020-11-10 16:14:40.321961	 20201110 161440.321961000



 select now() currenttime, from_unixtime(unix_timestamp( now() ),'yyyyMMddHHmmssSSSSS') str_time 

currenttime	str_time
 2020-11-10 16:15:38.403269	 2020111016153800000

缺少需求:
需要将时间戳精确到9位小数
已经此种时间戳想日期格式的转换?

impala 不提供常用的两种函数
1.将日期格式转为字符串
2.计算两个时间戳之间的差值,类似于别的数据库中的 timestamp_diff() 函数


impala 中的函数统计:

SELECT strleft('zeroifnull(DOUBLE)' ,INSTR('zeroifnull(DOUBLE)','(') -1) func_name
zeroifnull

查询impala支持的函数:
show functions in  _impala_builtins -- 查询内置函数

show functions -- 查询当前数据库支持的自定义函数

 

©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页