impala常用时间函数，date-＞string-＞timestamp互转

impala 和hive不一样，hive是弱类型，比如int和string在大部分条件下可以比较

比如hive select 1='1' --结果true或false

但是impala select 1='1' 报错 operands of type TINYINT and STRING are not comparable: 1 = '1'

这样带来的好处是类型一致结果更准确，缺点是增加开发人员的精力。。

话不多说直接来案例。

如何查看impala有哪些函数呢？

use _impala_builtins;

show functions like "*date*";

1.NOW()和current_timestamp() 返回当前时间，返回值是timestamp类型

select current_timestamp(),now()

2.current_date() 返回当前年月日，返回值是string类型

select current_date()

3.unix_timestamp() 返回当前时间戳，返回值bigInt类型

unix_timestamp()
unix_timestamp(STRING)
unix_timestamp(STRING, STRING)
unix_timestamp(TIMESTAMP)

select 'unix_timestamp()',unix_timestamp() union all
select 'unix_timestamp(NOW())' ,unix_timestamp(NOW())union all
select 'unix_timestamp(\'2023-01-01\')',unix_timestamp('2023-01-01')union all
select 'unix_timestamp(\'20230101\',\'yyyyMMdd\')',unix_timestamp('20230101','yyyyMMdd') union all
select 'unix_timestamp(\'2023-01-01 00:00:01\')',unix_timestamp('2023-01-01 00:00:01')

4.from_timestamp和 from_unixtime 返回的都是string类型，

注意这个impala没有date_format或者to_date这种行数对于标准的时间格式yyyy-MM-dd 想要格式化为自己需要的类型就需要这两个函数了

from_timestamp(TIMESTAMP, STRING)
from_unixtime(BIGINT)
from_unixtime(BIGINT, STRING)
from_unixtime(INT)
from_unixtime(INT, STRING)

select 'from_timestamp(NOW(),\'yyyy-MM-dd HH:mm:ss\') ',from_timestamp(NOW(),'yyyy-MM-dd HH:mm:ss') union all
select 'from_unixtime(cast (1695125510 as bigint)) ',from_unixtime(cast (1695125510 as bigint)) union all
select 'from_unixtime(cast (1695125510 as bigint),\'yyyy-MM\') ',from_unixtime(cast (1695125510 as bigint),'yyyy-MM') union all
select 'from_unixtime(1695125510) ',from_unixtime(1695125510) union all
select 'from_unixtime(1695125510,\'yyyy-MM-dd \')',from_unixtime(1695125510,'yyyy-MM-dd ')

所以2和3经常配合使用

demo1 20231122转为 2023-11-22

select "from_unixtime(unix_timestamp('20221122','yyyyMMdd'),'yyyy-MM-dd')", from_unixtime(unix_timestamp('20221122','yyyyMMdd'),'yyyy-MM-dd')

demo2 获取当前时间的各种格式

select "from_unixtime(unix_timestamp(),'yyyy-MM-dd')", from_unixtime(unix_timestamp(),'yyyy-MM-dd') union all
select "from_unixtime(unix_timestamp(),'yyyyMMddHHmmss')", from_unixtime(unix_timestamp(),'yyyyMMddHHmmss')

有人会说了为什么不直接用select now()，因为now()返回的是tmiestamp呀，from_unixtime是string一般来说我们更倾向于使用string。

5 add_months操作，入参说是date，timestamp也可以是string，返回值是timestamp

add_months(DATE, BIGINT)
add_months(DATE, INT)
add_months(TIMESTAMP, BIGINT)
add_months(TIMESTAMP, INT)

months_add(DATE, BIGINT)
months_add(DATE, INT)
months_add(TIMESTAMP, BIGINT)
months_add(TIMESTAMP, INT)

其中第二个参数int和bigint是一样的没什么区别就是一个方法的重载，支持不同类型的参数

select "add_months(NOW() ,0)",add_months(NOW() ,0) union all
select "add_months('2022-01-23',1)",add_months('2022-01-23',1) union all
select "add_months(NOW() ,cast(1 as bigint ))",add_months(NOW() ,cast(1 as bigint ))

6 adddate和date_add，入参date和timestamp，返回值说是有date和timestamp其实都是timestamp

天的增加

adddate(DATE, BIGINT)
adddate(DATE, INT)
adddate(TIMESTAMP, BIGINT)
adddate(TIMESTAMP, INT)
date_add(DATE, BIGINT)
date_add(DATE, INT)
date_add(TIMESTAMP, BIGINT)
date_add(TIMESTAMP, INT)

days_add(DATE, BIGINT)
days_add(DATE, INT)
days_add(TIMESTAMP, BIGINT)
days_add(TIMESTAMP, INT)

天的减少

date_sub(DATE, BIGINT)
date_sub(DATE, INT)
date_sub(TIMESTAMP, BIGINT)
date_sub(TIMESTAMP, INT)

subdate(DATE, BIGINT)
subdate(DATE, INT)
subdate(TIMESTAMP, BIGINT)
subdate(TIMESTAMP, INT)

其实和add_months操作一样

select 'adddate(now(),1)',adddate(now(),1) union all
select 'adddate(now(),-1)',adddate(now(),-1) union all
select 'date_add(now(),0)',date_add(now(),0) union all
select 'date_add(CURRENT_DATE(),1)',date_add(CURRENT_DATE(),1) union all
select 'date_add(current_date(),-1)',date_add(current_date(),-1)

7.同理还有时间参数的计算就不一一举例了。

年份的增加

weeks_add(DATE, BIGINT)
weeks_add(DATE, INT)
weeks_add(TIMESTAMP, BIGINT)
weeks_add(TIMESTAMP, INT)
周的增加

years_add(DATE, BIGINT)
years_add(DATE, INT)
years_add(TIMESTAMP, BIGINT)
years_add(TIMESTAMP, INT)

小时的增加

hours_add(TIMESTAMP, BIGINT)
hours_add(TIMESTAMP, INT)

分钟的增加

minutes_add(TIMESTAMP, BIGINT)
minutes_add(TIMESTAMP, INT)

毫秒的增加

milliseconds_add(TIMESTAMP, BIGINT)
milliseconds_add(TIMESTAMP, INT)

微秒的增加

microseconds_add(TIMESTAMP, BIGINT)
microseconds_add(TIMESTAMP, INT)

8 date_trunc() 对timestamp进去取整，返回类型是timestamp

date_trunc(STRING, DATE)

date_trunc(STRING, TIMESTAMP)

select "date_trunc('year', CURRENT_TIMESTAMP()) ",date_trunc('year', CURRENT_TIMESTAMP()) union all
select "date_trunc('month', CURRENT_TIMESTAMP()) ",date_trunc('month', CURRENT_TIMESTAMP()) union all
select "date_trunc('week', CURRENT_TIMESTAMP()) ",date_trunc('week', CURRENT_TIMESTAMP()) union all
select "date_trunc('day', CURRENT_TIMESTAMP()) ",date_trunc('day', CURRENT_TIMESTAMP()) union all
select "date_trunc('hour', CURRENT_TIMESTAMP()) ",date_trunc('hour', CURRENT_TIMESTAMP()) union all
select "date_trunc('minute', CURRENT_TIMESTAMP())",date_trunc('minute', CURRENT_TIMESTAMP())union all
select "date_trunc('second', CURRENT_TIMESTAMP())",date_trunc('second', CURRENT_TIMESTAMP())

9 datediff求日期的间隔，返回值是int 可以为负数

datediff(DATE, DATE)
datediff(TIMESTAMP, TIMESTAMP)

select "datediff('2023-01-04',now()) ",datediff('2023-01-04',now()) union all
select "datediff('2023-01-04',CURRENT_DATE())",datediff('2023-01-04',CURRENT_DATE())union all
select "datediff('2023-01-03','2023-01-01') ",datediff('2023-01-03','2023-01-01')

10 to_date 将timestamp标准化年月日，入参可以是string或者timestamp，返回值timestamp

to_date(TIMESTAMP)

其实还可以to_date(STRING)

select "to_date(now()) ",to_date(now()) union all
select "to_date('2023-1-2') ",to_date('2023-1-2') union all
select "to_date('2022-01-01')",to_date('2022-01-01')union all
select "to_date('2022/01/01')",to_date('2022/01/01')

11获取单独的年于日时分秒函数，当然也可以通过格式化获取
date_part(STRING, DATE)
date_part(STRING, TIMESTAMP)

select "date_part('year',now()) ",date_part('year',now()) union all
select "date_part('month',now()) ",date_part('month',now()) union all
select "date_part('day',now()) ",date_part('day',now()) union all
select "date_part('hour',now()) ",date_part('hour',now()) union all
select "date_part('minute',now())",date_part('minute',now())union all
select "date_part('second',now())",date_part('second',now())union all
select "YEAR(now()) ",YEAR(now()) union all
select "MONTH(now()) ",MONTH(now()) union all
select "day(now()) ",day(now()) union all
select "HOUR(now()) ",HOUR(now()) union all
select "MINUTE(now()) ",MINUTE(now()) union all
select "SECOND (now()) ",SECOND (now())

11 date_cmp 这个函数是比较两个日期大小的，可以看作简化版的datediff，返回值int

1代表第一个日期比第二个日期大。

date_cmp(DATE, DATE)

select "date_cmp('2023-09-22','2023-09-21')",date_cmp('2023-09-22','2023-09-21') union all
select "date_cmp('2023-09-22','2023-09-22')",date_cmp('2023-09-22','2023-09-22') union all
select "date_cmp('2023-09-22','2023-09-23')",date_cmp('2023-09-22','2023-09-23')

12 判断当前日期是今年第几天，这周的第几天

dayofmonth(DATE)
dayofmonth(TIMESTAMP)
dayofweek(DATE)
dayofweek(TIMESTAMP)
dayofyear(DATE)
dayofyear(TIMESTAMP)

select "DAYOFMONTH(CURRENT_DATE())",DAYOFMONTH(CURRENT_DATE()) union all
select "DAYOFWEEK(CURRENT_DATE()) ",DAYOFWEEK(CURRENT_DATE()) union all
select "DAYOFYEAR(CURRENT_DATE()) ",DAYOFYEAR(CURRENT_DATE())

注意这里dayofweek 可不是周六。当前时间2023-09-22 为啥显示是周六呢？应该是老外把周天当作第一天，所以周五就是第六天了。