impala 本年格式化时间_impala 下的SQL时间函数

#把时间转化成时间戳

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);

#取月份 无效月份为null

select hour('1970-01-01 15:30:00'),hour('1970-01-01 27:30:00');

#一周的第几天

select dayofweek('2004-06-13');

#英文下的星期几

select dayname('2004-06-13');

#两个时间差

select datediff('2019-11-10','2019-11-20');

把时间戳转换成秒数

select  unix_timestamp(now())

把秒数转成时间戳

select from_unixtime(cast(cast(1000.0 as decimal) as bigint));

把字符串转换成时间戳

cast('2019-10-14 18:00:41' as timestamp)

#增加月份

select now(), add_months(now(), 2);

select now(), add_months(now(), -1);

#当前时间

select now(), current_timestamp();

#加6小时

select now() as right_now,date_add(now(), interval 6 hours) as in_6_hours;

#加三周

select now() as right_now,date_add(now(), interval 3 weeks) as in_3_weeks;

#加三个月

select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31st';

#截取年份

select date_part('year',now()) as current_year;

#截取小时

select date_part('hour',now()) as hour_of_day;

#距现在之前的第七天

select now() as right_now,date_sub(now(), 7) as last_week;

#距现在之后第7天日期

select now() as right_now,date_sub(now(), -7) as last_week;

#前3周的那一天

select now() as right_now,date_sub(now(), interval 3 weeks) as 3_weeks_ago;

#6个小时前

select now() as right_now,date_sub(now(), interval 6 hours) as 6_hours_ago;

#上一个月

select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st';

#相差的天数

select now() as right_now, datediff(now() + interval 5 days,now()) as in_5_years;

#取天数

select now(), day(now());

#一周的第一天，英文下的星期几

select now() as right_now,dayofweek(now()) as todays_day_of_week,dayname(now()) as todays_day_name;

#截取年和月份

select now() as right_now,extract(year from now()) as this_year,extract(month from now()) as this_month;

#相差月份

select months_between('2015-02-28','2015-01-28');

#查询当前时间的季初日期

select now() as right_now,trunc(now(), 'Q') as current_quarter;

#两周之后的季初时间

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,weekofyear(now()) as this_week;

#之前的两周时间点

select now() as right_now,weeks_sub(now(), 2) as week_before_last;

#截取年份

select now() as right_now,year(now()) as this_year;

#增加一年

select now() as right_now,years_add(now(), 1) as next_year;