Hive SQL整数YYYYMM前几个月 [英] Hive SQL Integer YYYYMM previous Months

问题描述

我想创建一个查找最近2个月的SQL语句.

I want to create an SQL statement that looks for the last 2 months.

例如:

Select * 
from x
where sampledate<= YYYYMM-2

当前我正在使用此:

(year(from_unixtime(unix_timestamp()))*100+month(from_unixtime(unix_timestamp())))-1

但是它在一年的前两个月返回错误的声明:(

but it returns wrong statements for the first 2 months of a year :(

我的想法是先计算一个日期，然后将其更改为yyyymm整数格式.

My idea is to calculate with a date and then change it to a yyyymm integer format.

有什么想法吗?

推荐答案

您可以尝试以下方法吗:

Could you try this:

SELECT colomn
FROM table
WHERE date > (SELECT add_months(from_unixtime(unix_timestamp()),-2));

或者您可以使用:

SELECT colomn
FROM table
WHERE date > to_date(SELECT year(add_months(from_unixtime(unix_timestamp()),-2))+month(add_months(from_unixtime(unix_timestamp()),-2)));

与正则表达式和子字符串结合:

Combined with regex&substring:

SELECT colomn
FROM table
where sampledate>=substr(regexp_replace(add_months(from_unixtime(unix_timestamp()),-2), '-',''),1,6)

获取YYYYMM日期

这篇关于Hive SQL整数YYYYMM前几个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！