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)