impala 时间日期函数全解

た入场券 2023-01-09 15:31 381阅读 0赞

参考文章：[ impala 时间日期函数全解][impala]

[impala 下的SQL时间函数][impala _SQL]

--======================================================================
    ---------------------------------- use ---------------------------------
    --======================================================================
    	SELECT datadate,session_info
    		FROM database
    		WHERE datadate >= NOW() - INTERVAL 5 DAY
    		ORDER BY datadate DESC;
    --…………………………………………………………………………………………	
    	SELECT datadate,session_info
    		FROM database
    		WHERE datadate >= from_unixtime(unix_timestamp(now() - interval 5 days),'yyyyMMdd')
    		GROUP BY datadate
    		ORDER BY datadate DESC;	
    --…………………………………………………………………………………………	
    	SELECT from_unixtime(unix_timestamp(create_date),'yyyy-MM-dd HH:mm:ss') FROM health_assess_data
    --======================================================================
    ---------------------------- hive 日期函数 -----------------------------
    --======================================================================
    --格式化日期
    	from_unixtime(bigint unixtime[, string format])
    	from_unixtime(int, 'yyyy/MM/dd HH:mm')
    	
    	--注意参数
    	Return type: string
    	-- 将指定的时间戳,格式化为字符串. 时间戳参数应该是秒数格式, 
    	-- 所以该参数需要用 unix_timestamp() 包一下.
    	-- 注意月份和分钟对应的格式字符串, 常用的格式有:
    		"yyyy-MM-dd HH:mm:ss.SSSSSS", 
    		"yyyy-MM-dd HH:mm:ss", 
    		"dd/MM/yyyy HH:mm:ss.SSSSSS", 
    		"MMM dd, yyyy HH.mm.ss (SSSSSS)"
    		
    	usage:
    		from_unixtime(1392394861,"yyyy-MM-dd");
    		select from_unixtime(1392394861,"yyyy-MM-dd");
    		--把秒数转成时间戳
    		select from_unixtime(cast(cast(1000.0 as decimal) as bigint));
    		
    		SELECT from_unixtime(unix_timestamp(create_date),'yyyy-MM-dd HH:mm:ss') FROM health_assess_data
    
    --……………………………………………………………………………………………………	
    -- 时间戳取整
    	--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
    
    --……………………………………………………………………………………………………
    -- 时间加减
    	时间戳可以直接加减 interval n days/months/years/hours/minutes .
    
    	也可以使用下面的函数:
    	years_add(timestamp t, int n)
    	years_sub(timestamp t, int n)
    	months_add(timestamp t, int n)
    	months_sub(timestamp t, int n)
    	days_add(timestamp t, int n)
    	days_sub(timestamp t, int n)
    	hours_add(timestamp t, int n)
    	hours_sub(timestamp t, int n)
    	minutes_add(timestamp t, int n)
    	minutes_sub(timestamp t, int n)
    
    	--也可以用下面两个通用的函数:
    	date_add(timestamp startdate, int days)
    	date_add(timestamp startdate, interval_expression)
    	date_sub(timestamp startdate, int days)
    	date_sub(timestamp startdate, interval_expression)
    	
    --……………………………………………………………………………………………………
    -- 时间戳提取
    	date_part('year', now())
    	extract(now(), 'year')
    	extract(year from now())
    
    --……………………………………………………………………………………………………
    --把时间转化成时间戳
    	select cast('1966-07-30' as timestamp);
    	select cast('1985-09-25 17:45:30.005' as timestamp);
    	select cast('08:30:00' as timestamp);
    
    --……………………………………………………………………………………………………
    --把字符串转换成时间戳
    	cast('2019-10-14 18:00:41' as timestamp);
    
    --……………………………………………………………………………………………………
    --增加月份
    	add_months(timestamp date, int months)
    	add_months(timestamp date, bigint months)
    	Return type: timestamp
    
    	usage: 
    		--增加月份
    		add_months(now(),1)
    		select now(), add_months(now(), 2);
    		select now(), add_months(now(), -1);
    
    --……………………………………………………………………………………………………
    --增加日期
    	adddate(timestamp startdate, int days), 
    	adddate(timestamp startdate, bigint days)
    	Return type: timestamp
    	
    	usage:adddate(now(),1)
    
    --……………………………………………………………………………………………………
    --当前时间戳
    	current_timestamp()和now()等价
    
    --……………………………………………………………………………………………………
    --日期相减
    	datediff(string enddate, string startdate)
    	Return type: int
    	
    	usage:
    		datediff("2018-08-05", "2018-08-03")
    		--相差的天数
    		select now() as right_now, datediff(now() + interval 5 days,now()) as in_5_years;
    		select datediff('2019-11-10','2019-11-20');
    	
    	-- 两个时间戳比较
    		datediff(timestamp enddate, timestamp startdate) ,相差多少天, 精度是天
    		timestamp_cmp(now() + interval 70 minutes, now()), 比较两个时间戳的大小, 本例的结果为 1
    		impala 没有好用的 timestamp_diff() 函数, 比如我们想要知道两个时间相差多少个小时, 不能直接求出, 下面是一个简单的步骤:
    		1. 先算出一个小时对应的秒数是多少
    		2. 将两个时间都转成秒数, 然后做差, 然后除以一个小时的秒数.
    --……………………………………………………………………………………………………
    --得到天，得到月份
    	day(string date)
    	Return type: int
    	
    	usage: 
    		day("2018-08-05")
    		--取天数
    		select now(), day(now());
    
    --……………………………………………………………………………………………………
    --得到星期英文
    	dayname(string date) 
    	Return type: string
    	
    	usage:
    		dayname("2018-08-05") Sunday
    		--英文下的星期几
    		select dayname('2004-06-13');
    
    --……………………………………………………………………………………………………		
    --得到这一天是这周的第几天
    	dayofweek(string date)  1 (Sunday) to 7 (Saturday).
    	Return type: int
    	
    	usage:
    		dayofweek("2018-08-06")
    		
    		--一周的第一天，英文下的星期几
    		select now() as right_now, dayofweek(now()) as todays_day_of_week,dayname(now()) as todays_day_name;
    
    		--一周的第几天
    		select dayofweek('2004-06-13');
    
    --……………………………………………………………………………………………………		
    --加天数
    	days_add(timestamp startdate, int days) 
    	Return type: timestamp
    	usage:days_add(now(),2)
    
    
    --……………………………………………………………………………………………………
    --减天数
    	days_sub(timestamp startdate, int days)
    	Return type: timestamp
    	
    	usage:days_sub(now(), 2)
    
    --……………………………………………………………………………………………………
    --得到小时
    	hour(string date)
    	Return type: int
    	usage:
    		hour("2018-08-06 12:32:54")
    		
    		--取月份 无效月份为null
    		select hour('1970-01-01 15:30:00'),hour('1970-01-01 27:30:00');
    
    --……………………………………………………………………………………………………		
    --增加小时
    	hours_add(timestamp date, int hours)
    	Return type: timestamp
    	usage:hours_add(now(),2)
    
    --……………………………………………………………………………………………………
    --减少
    	hours_sub(timestamp date, int hours)
    	Return type: timestamp
    	usage:hours_sub(now(),2)
    
    --……………………………………………………………………………………………………
    --得到分钟
    	minute(string date)
    	Return type: int
    	usage:minute(now())
    
    --……………………………………………………………………………………………………
    --增加分钟
    	minutes_add(timestamp date, int minutes)
    	Return type: timestamp
    	
    	usage:minutes_add(now(),2)
    
    --……………………………………………………………………………………………………
    --减少分钟
    	minutes_sub(timestamp date, int minutes)
    	Return type: timestamp
    	
    	usage:minutes_sub(now(),2)
    
    --……………………………………………………………………………………………………
    --加三个月
    	select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31st';
    
    --……………………………………………………………………………………………………
    --加三周
    	select now() as right_now, date_add(now(), interval 3 weeks) as in_3_weeks;
    
    --……………………………………………………………………………………………………
    --加6小时
    	select now() as right_now, date_add(now(), interval 6 hours) as in_6_hours;
    
    --……………………………………………………………………………………………………
    --得到月份
    	month(string date)
    	Return type: int
    	
    	usage:month("2018-08-06 12:32:54")
    
    --……………………………………………………………………………………………………
    --上一个月
    	select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st';
    
    --……………………………………………………………………………………………………
    --6个小时前
    	select now() as right_now, date_sub(now(), interval 6 hours) as 6_hours_ago;
    
    --……………………………………………………………………………………………………
    --前3周的那一天
    	select now() as right_now,date_sub(now(), interval 3 weeks) as 3_weeks_ago;
    
    --……………………………………………………………………………………………………
    --距现在之后第7天日期
    	select now() as right_now,date_sub(now(), -7) as last_week;
    
    --……………………………………………………………………………………………………
    --距现在之前的第七天
    	select now() as right_now,date_sub(now(), 7) as last_week;
    
    --……………………………………………………………………………………………………
    --截取小时
    	select date_part('hour',now()) as hour_of_day;
    
    --……………………………………………………………………………………………………
    --截取年份
    	select date_part('year',now()) as current_year;
    
    --……………………………………………………………………………………………………
    --月份相加
    	months_add(timestamp date, int months)
    	Return type: timestamp
    	usage:months_add(now(),3)
    
    --……………………………………………………………………………………………………
    --减月份
    	months_sub(timestamp date, int months)
    	Return type: timestamp
    	
    	months_sub(now(),3)
    
    --……………………………………………………………………………………………………
    --相差月份
    	select months_between('2015-02-28','2015-01-28');
    	last_day(timestamp t)
    	months_between(timestamp newer, timestamp older)
    --……………………………………………………………………………………………………
    --截取年和月份
    	select now() as right_now, extract(year from now()) as this_year, extract(month from now()) as this_month;
    
    --……………………………………………………………………………………………………
    --得到秒
    	second(string date)
    	Return type: int
    
    --……………………………………………………………………………………………………
    --秒加
    	seconds_add(timestamp date, int seconds)
    	Return type: timestamp
    
    --……………………………………………………………………………………………………
    --秒减
    	seconds_sub(timestamp date, int seconds)
    	Return type: timestamp
    
    --……………………………………………………………………………………………………
    -- 将秒数转换成时间戳
    	to_timestamp(bigint unixtime)
    
    --……………………………………………………………………………………………………
    -- 将字符串转换成时间戳
    	to_timestamp(string date, string pattern)
    	--说明: impala 没有直接将时间戳转换为字符串的函数, 所以经常的写法是: from_unixtime(unix_timestamp( t1 ),'yyyyMMdd HH:mm')
    
    --……………………………………………………………………………………………………	
    -- 将时间戳转换为日期字符串
    	to_date(now())
    	--将指定时间戳转换为日期字符串, 日期格式为 yyyy--MM-dd 
    	to_date(timestamp) 
    
    --……………………………………………………………………………………………………
    --当前时间戳相对于 linux epoch 的秒数, 不带参数, 返回 '1970-01-01 00:00:00' UTC 到现在的秒数
    	unix_timestamp() 
    	
    	unix_timestamp(string datetime),
    	unix_timestamp(string datetime, string format),
    	unix_timestamp(timestamp datetime)
    	Return type: bigint
    	
    	--把时间戳转换成秒数
    	select  unix_timestamp(now())
    	
    	-- 转换到相对于 linux epoch 的秒数
    	unix_timestamp(now()+ interval 3 days), 如果传入 timestamp 参数, 返回该时间戳相对于 linux epoch 的秒数
    	unix_timestamp(string datetime, string format), 还支持传入时间字符串, 返回值还是相对于 linux epoch 的秒数
    
    --……………………………………………………………………………………………………		
    --得到这周是这年的多少周
    	weekofyear(string date)
    	Return type: int
    
    	usage:
    		weekofyear("2018-08-06 12:32:54")
    		--一年中的第几周
    		select now() as right_now, weekofyear(now()) as this_week;
    
    --……………………………………………………………………………………………………
    --周加
    	weeks_add(timestamp date, int weeks)
    	Return type: timestamp
    	
    	usage:weeks_add("2018-08-06 12:32:54", 1)
    --……………………………………………………………………………………………………
    --两周之后的季初时间
    	select now() + interval 2 weeks as 2_weeks_from_now, trunc(now() + interval 2 weeks, 'Q') as still_current_quarter;
    
    --……………………………………………………………………………………………………
    --查询当前时间的季初日期
    	select now() as right_now, trunc(now(), 'Q') as current_quarter;
    
    --……………………………………………………………………………………………………
    --周减
    	weeks_sub(timestamp date, int weeks)
    	Return type: timestamp
    	
    	usage:
    		weeks_sub("2018-08-06 12:32:54", 1)
    
    		--之前的两周时间点
    		select now() as right_now,weeks_sub(now(), 2) as week_before_last;
    
    --……………………………………………………………………………………………………
    --得到年
    	year(string date)
    	Return type: int
    	--截取年份
    	select now() as right_now,year(now()) as this_year;
    
    --……………………………………………………………………………………………………
    --年加
    	years_add(timestamp date, int years)
    	Return type: timestamp
    	
    	--增加一年
    	select now() as right_now, years_add(now(), 1) as next_year;
    
    --……………………………………………………………………………………………………
    --年减
    	years_sub(timestamp date, int years)
    	Return type: timestamp

[impala]: https://blog.csdn.net/qq_22222499/article/details/81463516
[impala _SQL]: https://www.cnblogs.com/hello-wei/p/11895982.html