Question

Filter out weekends in Redshift

  • 15 April 2016
  • 1 reply
  • 329 views

I would expect the following two filters to return the exact same results.


First filter




Second filter




However, the second filter returns no results. Here’s the SQL where clause generated by the first filter:





WHERE (MOD(EXTRACT(DOW FROM paid_digital.date)::integer - 1 + 7, 7) IN (0,1,2,3,4))





and now the second:





WHERE (MOD(EXTRACT(DOW FROM paid_digital.date)::integer - 1 + 7, 7) IN (-6,-5))





I’m pretty sure this is a bug in the Redshift dialect. In the meantime, if you run into this issue, just select MON-FRI as I did in the first example.


1 reply

Userlevel 6
Badge

Yes indeed, that is a bug.





WHERE (MOD(EXTRACT(DOW FROM paid_digital.date)::integer - 1 + 7, 7) IN (-6,-5))





Should be





WHERE (MOD(EXTRACT(DOW FROM paid_digital.date)::integer - 1 + 7, 7) NOT IN (6,5))



Reply