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:

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    Answer ✓

    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)
    
    

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

  • Jim_KnicelyJim_Knicely Administrator

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.