我想在Impala查询中获得以下日期:
a. Sunday to Saturday Week (SSW):
1. First and Last day of Current week (SSW)
2. First and Last day of Previous week(SSW)
b. Monday to Sunday Week (MSW):
1. First and Last day of Current week (MSW)
2. First and Last day of Previous week(MSW)
c. Month:
1. First and Last Day of Current Month
2. First and Last Day of Previous Month
d. Quarter:
1. First and Last Day of Current Quarter
2. First and Last Day of Previous Quarter
e. Year:
1. First and Last Day of Current Year
2. First and Last Day of Previous Year
这是我到目前为止所拥有的:
SELECT
--Month
date_add(last_day(add_months(current_timestamp(),-1)),1) as Frist_Day_of_Current_Month,
last_day(now()) as Last_Day_of_Current_Month,
date_add(last_day(add_months(current_timestamp(),-2)),1) as Frist_Day_of_Previous_Month,
last_day(add_months(current_timestamp(),-1)) as Last_Day_of_Previous_Month,
--Year
trunc(now(), 'Y') as Frist_Day_of_Current_Year,
date_sub(trunc(now(), 'YEAR'), 1) AS Last_Day_of_Previous_Year
最佳答案
以下是日期:
选择
-单日
TRUNC(NOW()，'DD')今天，
昨天(ADDDATE(TRUNC(NOW()，'DD')，-1)
ADDDATE(TRUNC(NOW()，'DD')，-2)AS TWO_DAYS_AGO，
ADDDATE(TRUNC(NOW()，'DD')，-3)AS THREE_DAYS_AGO，
ADDDATE(TRUNC(NOW()，'DD')，-4)AS FOUR_DAYS_AGO，
ADDDATE(TRUNC(NOW()，'DD')，-5)AS FIVE_DAYS_AGO，
ADDDATE(TRUNC(NOW()，'DD')，-6)AS SIX_DAYS_AGO，
ADDDATE(TRUNC(NOW()，'DD')，-7)AS WEEK_AGO，
- 周
--SUNDAY TO SATURDAY (NEED TO SCHEDULE THE REPORT TO RUN ON ONLY ON MONDAYS)
TRUNC(NOW()，'D')-在FIRST_DAY_OF_CURRENT_WEEK_SS间隔1天，
TRUNC(NOW()，'D')+ INTERVAL 5 DAY as LAST_DAY_OF_CURRENT_WEEK_SS，
TRUNC(NOW()，'D')-在FIRST_DAY_OF_PREVIOUS_WEEK_SS间隔8天，
TRUNC(NOW()，'D')-间隔为LAST_DAY_OF_PREVIOUS_WEEK_SS，为期2天，
--MONDAY TO SUNDAY (NEED TO SCHEDULE THE REPORT TO RUN ONLY ON MONDAYS)
TRUNC(NOW()，'DY')AS FRIST_DAY_OF_CURRENT_WEEK_MS，
TRUNC(NOW()，'DY')+ INTERVAL 6 DAY AS LAST_DAY_OF_CURRENT_WEEK_MS，
TRUNC(NOW()，'DY')-在FIRST_DAY_OF_PREVIOUS_WEEK_MS间隔7天，
TRUNC(NOW()，'DY')-在LAST_DAY_OF_PREVIOUS_WEEK_MS间隔1天，
- 月
DATE_ADD(LAST_DAY(ADD_MONTHS(CURRENT_TIMESTAMP()，-1))，1)AS FRIST_DAY_OF_CURRENT_MONTH，
LAST_DAY(NOW())作为LAST_DAY_OF_CURRENT_MONTH，
DATE_ADD(LAST_DAY(ADD_MONTHS(CURRENT_TIMESTAMP()，-2))，1)AS FRIST_DAY_OF_PREVIOUS_MONTH，
LAST_DAY(ADD_MONTHS(CURRENT_TIMESTAMP()，-1))作为LAST_DAY_OF_PREVIOUS_MONTH，
- 25美分硬币
TRUNC(NOW()，'Q')AS FIRST_DAY_OF_CURRENT_QUARTER，
TRUNC(NOW()，'Q')+间隔3个月-LAST_DAY_OF_CURRENT_QUARTER间隔1天，
TRUNC(NOW()，'Q')-在FIRST_DAY_OF_PREVIOUS_QUARTER间隔3个月，
TRUNC(NOW()，'Q')-间隔LAST_DAY_OF_PREVIOUS_QUARTER 1天，
- 年
TRUNC(NOW()，'Y')AS FRIST_DAY_OF_CURRENT_YEAR，
TRUNC(NOW()，'YEAR')+间隔1年-LAST_DAY_OF_CURRENT_YEAR间隔1天，
TRUNC(NOW()，'YEAR')-间隔1年(FRIST_DAY_OF_PREVIOUS_YEAR)，
TRUNC(NOW()，'Y')-间隔为LAST_DAY_OF_PREVIOUS_YEAR 1天
谢谢，
问候，
艾哈迈德