Where clause = the week ending Sunday in postgreSQL

Question

In my where clause I have to manually change the weeks for both the clauses ("Week" and "Can_Week_End"). And in my example, the weeks end on Sundays (12/4,12/11,12/18, etc...). How can I manipulate the where clause filter so it does the date changing automatically. If someone queries out

select * from myview

Monday morning, it will show the data correspondent to the last week ending on the passed Sunday (day before). Is there a way to accomplish this? Please provide insights.

create view myview 
as 
select case when sd."Build_Spec" = 'Spec' then 'X' else '' end as "Spec", 
       case when sd."Build_Spec" = 'Build' then 'X' else '' end as "Build", 
       Case when sd."Can_Week_End" = '' then '' else 'X' end as "Cancel",
sd."Price", 
sd."Sq_Ft" as "SF", 
sd."Price"::money / to_number(sd."Sq_Ft",'99999999') as "PPSF", 
case when sd."Realtor_Sale" is not null then 'X' else '' end as "Realtor"
from datapcr sd 
left join comppcr ad on "Address" =  "Address_of_New_Home"
where sd."Week" = '2016-12-18'or sd."Can_Week_End" = '12/18/2016'
order by "Cancel"

This is one of those situations where a calendar table really comes in handy. You could make weekEnding one of the fields. — Dan Bracuk, Dec 22 '16 at 20:49

score 0 · Accepted Answer · edited May 23 '17 at 11:45

It's unclear why you have two different date formats in the same query, and it's also unclear where you want to draw the line exactly. but basically, you can solve this with date_trunc():

...
where sd."Week" = date_trunc('week', now())::date - 1
...

date_trunc('week', now()) returns Mon, 00:00 of the current week. Cast to date and subtract 1 to get the date of last Sunday. Voilá.

How do I determine the last day of the previous month using PostgreSQL?

Where clause = the week ending Sunday in postgreSQL

1 Answers1