Search code examples
hadoophiveimpala

Impala Dates for Weeks, Months, Quarters, & Years

I would like to get the following dates in Impala query:

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

This is what I have so far:

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
Solution

  • Here are the dates:

    SELECT --SINGLE DAYS

    TRUNC(NOW(),'DD') AS TODAY,

    ADDDATE(TRUNC(NOW(),'DD'), -1) AS YESTERDAY,

    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,

    --WEEK 

    --SUNDAY TO SATURDAY (NEED TO SCHEDULE THE REPORT TO RUN ON ONLY ON MONDAYS)

    TRUNC(NOW(), 'D') - INTERVAL 1 DAY AS FIRST_DAY_OF_CURRENT_WEEK_SS,

    TRUNC(NOW(), 'D') + INTERVAL 5 DAY AS LAST_DAY_OF_CURRENT_WEEK_SS,

    TRUNC(NOW(), 'D') - INTERVAL 8 DAY AS FIRST_DAY_OF_PREVIOUS_WEEK_SS,

    TRUNC(NOW(), 'D') - INTERVAL 2 DAY AS LAST_DAY_OF_PREVIOUS_WEEK_SS, 

    --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') - INTERVAL 7 DAY AS FIRST_DAY_OF_PREVIOUS_WEEK_MS,

    TRUNC(NOW(),'DY') - INTERVAL 1 DAY AS LAST_DAY_OF_PREVIOUS_WEEK_MS,

    --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,

    --QUARTER

    TRUNC(NOW(), 'Q') AS FIRST_DAY_OF_CURRENT_QUARTER,

    TRUNC(NOW(), 'Q')+ INTERVAL 3 MONTHS - INTERVAL 1 DAY AS LAST_DAY_OF_CURRENT_QUARTER,

    TRUNC(NOW(), 'Q') - INTERVAL 3 MONTHS AS FIRST_DAY_OF_PREVIOUS_QUARTER,

    TRUNC(NOW(), 'Q') - INTERVAL 1 DAY AS LAST_DAY_OF_PREVIOUS_QUARTER,

    --YEAR

    TRUNC(NOW(), 'Y') AS FRIST_DAY_OF_CURRENT_YEAR,

    TRUNC(NOW(), 'YEAR') + INTERVAL 1 YEAR - INTERVAL 1 DAY AS LAST_DAY_OF_CURRENT_YEAR,

    TRUNC(NOW(), 'YEAR') - INTERVAL 1 YEAR AS FRIST_DAY_OF_PREVIOUS_YEAR,

    TRUNC(NOW(), 'Y') - INTERVAL 1 DAY AS LAST_DAY_OF_PREVIOUS_YEAR

    Thanks,

    Regards, Ahmed