获取本季度上季度的时间_hive时间函数应用小例

傷城~ 2022-10-07 11:44 547阅读 0赞

### 1. 获取当前时间：yyyy-mm-dd ###

> SELECT CURRENT\_DATE;
> 
> current\_date()  
> 2021-06-17  
> Time taken: 0.077 seconds, Fetched 1 row(s)

### 2. 获取当月第一天时间：yyyy-mm-dd ###

select date\_sub(current\_date,dayofmonth(current\_date)-1);

> date\_sub(current\_date(), (dayofmonth(current\_date()) - 1))  
> 2021-06-01  
> Time taken: 0.076 seconds, Fetched 1 row(s)

### 3. 获取下个月第一天时间：yyyy-mm-dd ###

> select add\_months(date\_sub(current\_date,dayofmonth(current\_date)-1),1);
> 
> add\_months(date\_sub(current\_date(), (dayofmonth(current\_date()) - 1)), 1)  
> 2021-07-01  
> Time taken: 0.05 seconds, Fetched 1 row(s)

### 4. 获取当月第几天：yyyy-mm-dd ###

> select dayofmonth(current\_date);
> 
> dayofmonth(current\_date())  
> 17  
> Time taken: 0.066 seconds, Fetched 1 row(s)

### 5. 获取当前日期所在月月末日期：yyyy-mm-dd ###

> select last\_day(current\_date);
> 
> last\_day(current\_date())  
> 2021-06-30  
> Time taken: 0.039 seconds, Fetched 1 row(s)

### 6. 获取当前日期本周一：yyyy-mm-dd ###

> select date\_sub(next\_day(CURRENT\_DATE,'MO'),7);
> 
> date\_sub(next\_day(current\_date(), MO), 7)  
> 2021-06-14  
> Time taken: 0.045 seconds, Fetched 1 row(s)

### 7. 获取当前日期上周一：yyyy-mm-dd ###

> select date\_sub(next\_day(CURRENT\_DATE,'MO'),14);
> 
> date\_sub(next\_day(current\_date(), MO), 14)  
> 2021-06-07  
> Time taken: 0.067 seconds, Fetched 1 row(s)

### 8. 获取当前日期上周日：yyyy-mm-dd ###

> select date\_sub(next\_day(CURRENT\_DATE,'MO'),8);
> 
> date\_sub(next\_day(current\_date(), MO), 8)  
> 2021-06-13  
> Time taken: 0.033 seconds, Fetched 1 row(s)

### 9. 获取当前日期本周二：yyyy-mm-dd（获取其他周几调整最后参数） ###

> select date\_sub(next\_day(CURRENT\_DATE,'MO'),6);
> 
> date\_sub(next\_day(current\_date(), MO), 6)  
> 2021-06-15  
> Time taken: 0.041 seconds, Fetched 1 row(s)

###    10. 获取当前日期上周二： ###

yyyy-mm-dd（获取其他周几调整最后参数）

> select date\_sub(next\_day(CURRENT\_DATE,'MO'),13) ;
> 
> date\_sub(next\_day(current\_date(), MO), 13)  
> 2021-06-08  
> Time taken: 0.059 seconds, Fetched 1 row(s)

###    11. 获取当前时间的前/后几个月时间： ###

yyyy-mm-dd（调整最后参数）

> select add\_months(CURRENT\_DATE,-3);
> 
> add\_months(current\_date(), -3)  
> 2021-03-17  
> Time taken: 0.04 seconds, Fetched 1 row(s)

### 12. 开始结束时间相差天数 ###

datediff(string enddate, string startdate);

> spark-sql> select datediff('2021-06-15','2021-06-20');  
> datediff(CAST(2021-06-15 AS DATE), CAST(2021-06-20 AS DATE))  
> \-5  
> Time taken: 0.026 seconds, Fetched 1 row(s)

### 13. 从开始时间startdate加上days ###

date\_add(string startdate, int days)

> select date\_add('2021-06-15', 5);
> 
> date\_add(CAST(2021-06-15 AS DATE), 5)  
> 2021-06-20  
> Time taken: 0.058 seconds, Fetched 1 row(s)

### 14. 从开始时间startdate减去days ###

date\_sub(string startdate, int days);

> select date\_sub('2021-06-15', 5);
> 
> date\_sub(CAST(2021-06-15 AS DATE), 5)  
> 2021-06-10  
> Time taken: 0.067 seconds, Fetched 1 row(s)

### 15. 获取上季度初日期 ###

> select add\_months(concat(year(CURRENT\_DATE),'-',substr(concat('0',floor((month(CURRENT\_DATE)+2)/3)\*3+1),-2),'-01'),-6);
> 
> add\_months(CAST(concat(CAST(year(current\_date()) AS STRING), -, substring(concat(0, CAST(((FLOOR((CAST((month(current\_date()) + 2) AS DOUBLE) / CAST(3 AS DOUBLE))) \* CAST(3 AS BIGINT)) + CAST(1 AS BIGINT)) AS STRING)), -2, 2147483647), -01) AS DATE), -6)  
> 2021-01-01  
> Time taken: 0.09 seconds, Fetched 1 row(s)

### 16. 获取本季度初日期 ###

> select add\_months(concat(year(CURRENT\_DATE),'-',substr(concat('0',floor((month(CURRENT\_DATE)+2)/3)\*3+1),-2),'-01'),-3);
> 
> add\_months(CAST(concat(CAST(year(current\_date()) AS STRING), -, substring(concat(0, CAST(((FLOOR((CAST((month(current\_date()) + 2) AS DOUBLE) / CAST(3 AS DOUBLE))) \* CAST(3 AS BIGINT)) + CAST(1 AS BIGINT)) AS STRING)), -2, 2147483647), -01) AS DATE), -3)  
> 2021-04-01  
> Time taken: 0.058 seconds, Fetched 1 row(s)

### 17.取上个季度同今天时间 ###

> select add\_months(CURRENT\_DATE,-3);
> 
> add\_months(current\_date(), -3)  
> 2021-03-17  
> Time taken: 0.05 seconds, Fetched 1 row(s)

### 18.去年本季度开始时间 ###

> select add\_months(concat(year(CURRENT\_DATE),'-',substr(concat('0',floor((month(CURRENT\_DATE)+2)/3)\*3+1),-2),'-01'),-15);
> 
> add\_months(CAST(concat(CAST(year(current\_date()) AS STRING), -, substring(concat(0, CAST(((FLOOR((CAST((month(current\_date()) + 2) AS DOUBLE) / CAST(3 AS DOUBLE))) \* CAST(3 AS BIGINT)) + CAST(1 AS BIGINT)) AS STRING)), -2, 2147483647), -01) AS DATE), -15)  
> 2020-04-01  
> Time taken: 0.06 seconds, Fetched 1 row(s)

### 19.去年本季度今日时间 ###

> select add\_months(CURRENT\_DATE,-12);
> 
> add\_months(current\_date(), -12)  
> 2020-06-17  
> Time taken: 0.024 seconds, Fetched 1 row(s)