The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Prior week
I need a Vertica statement to extract the prior week's data Sun - Sat. For example, if today is 12/2/2021, I need all dates between Sunday, Nov 21st and Saturday, Nov 27th (Sun-Sat)
Select aux_date from data
where aux_date between Nov 21 and Nov 27
I can't seem to figure this out for Vertica?
Tagged:
0
Best Answer
-
Jim_Knicely
Administrator
Probably a million ways to get the results...
dbadmin=> SELECT aux_date FROM data ORDER BY aux_date; aux_date ------------ 2021-11-20 2021-11-21 2021-11-22 2021-11-23 2021-11-24 2021-11-25 2021-11-26 2021-11-27 2021-11-28 2021-12-26 2021-12-27 2021-12-28 2021-12-29 2021-12-30 2021-12-31 2022-01-01 2022-01-02 2022-01-03 2022-01-04 (19 rows) dbadmin=> SELECT sysdate, * FROM data WHERE aux_date BETWEEN TRUNC(SYSDATE-7, 'IW')-1 AND NEXT_DAY(TRUNC(SYSDATE-7, 'IW'), 'SATURDAY'); sysdate | aux_date ----------------------------+------------ 2021-12-02 22:15:59.324078 | 2021-11-21 2021-12-02 22:15:59.324078 | 2021-11-22 2021-12-02 22:15:59.324078 | 2021-11-23 2021-12-02 22:15:59.324078 | 2021-11-24 2021-12-02 22:15:59.324078 | 2021-11-25 2021-12-02 22:15:59.324078 | 2021-11-26 2021-12-02 22:15:59.324078 | 2021-11-27 (7 rows) dbadmin=> SELECT * FROM data WHERE aux_date BETWEEN TRUNC('01/02/2022'::DATE, 'IW')-1 AND NEXT_DAY(TRUNC('01/02/2022'::DATE, 'IW')-1, 'SATURDAY'); aux_date ------------ 2021-12-26 2021-12-27 2021-12-28 2021-12-29 2021-12-30 2021-12-31 2022-01-01 (7 rows)1
Answers
This seems like it works:
select CURRENT_DATE() - DATE_PART('DOW',CURRENT_DATE())-1 as Prior Saturday
select CURRENT_DATE() - DATE_PART('DOW',CURRENT_DATE())-7 as Sunday before prior Saturday
Here's another way that I think will work for you:
dbadmin=> SELECT aux_date FROM data; aux_date ------------ 2021-11-20 2021-11-21 2021-11-22 2021-11-23 2021-11-24 2021-11-25 2021-11-26 2021-11-27 2021-11-28 (9 rows) dbadmin=> SELECT * FROM data WHERE WEEK(aux_date) = WEEK('12/2/2021') - 1; aux_date ------------ 2021-11-21 2021-11-22 2021-11-23 2021-11-24 2021-11-25 2021-11-26 2021-11-27 (7 rows)The problem with that though is when weeks pass over years