First and Last day of the previous week with Postgresql

Question

I'm trying to find a way to query for the first day and last day of the previous week with postgresql.

I've found the answer for both mysql ,sql server and other but couldn't find it for PSQL.

I've been trying using current_date and intervals 1 week or - integer '7' ( BETWEEN current_date - INTERVAL '1 weeks' and current_date ) but it just gives me 1 week back from the current day and not the first or last day in store for the previous week.

I'm looking for something dynamic : The first day of the previous week could be tuesday and the last day thursday it's not always monday and friday depends what has been stored for the previous week.

I need to be able to retreive a result for the 1st day of the previous week aswell as the last day so it's going to be 2 queries i believe.

edit : My main issue is that most answer around the same problem use current_date / now() and subtract a fix number of days to retrieve the first day of the previous week. But the query should be dynamic it won't be done on the same day every week - so the subtraction of days will be different depending on which day the query is performed - AND the first day of previous week that i have in store isn't always monday and last day isn't always friday. Sometimes i'll only have 3 days worth of data for a week so the first day will be tuesday and last day thursday and i wouldn't want to return NULL for monday and Null for friday

edit 2 :

dummy data looks like that :

id |  date         
 1    monday(w1)      
 2    tuesday(w1)
 3    wednesday(w1)
 4    thursday(w1)
 5    friday(w1)
 6    tuesday(w2)
 7    wednesday(w2)
 8    friday(w2)
 9    monday(w3)
10    tuesday(w3)
    --query ran here--   

Obviously my dates are stored as actual date format ( 2019-10-24 ) but on the date of the query ( hypnotically wednesday ) i'd want the first item of the previous week to be id 6 and the last item to be id 8

Answer 1

From what I'm reading, you're looking for the first and last stored day of the week, so:

WITH
  first_stored AS (SELECT min(stored_date) as first FROM stored WHERE stored_date > DATE_TRUNC('WEEK', NOW()) - INTERVAL '8 DAY'),
  last_stored AS (SELECT max(stored_date) as last FROM stored WHERE stored_date < DATE_TRUNC('WEEK', NOW()) - INTERVAL '1 DAY')
SELECT first_stored.first, last_stored.last FROM first_stored, last_stored;

This yields:

   first    |    last    
------------+------------
 2019-10-15 | 2019-10-18  -- a Tuesday and a Thursday
(1 row)

UPDATE:

Assuming "today" is 2019-10-26 (the timestamp of your latest edit to the question), here's a version that fits with the example you shared:

postgres=# create table my_table (id int, stored_date date);
CREATE TABLE
postgres=# insert into my_table values (1,'2019-10-07'),(2,'2019-10-08'),(3,'2019-10-09'),(4,'2019-10-10'),(5,'2019-10-11'),(6,'2019-10-15'),(7,'2019-10-16'),(8,'2019-10-18'),(9,'2019-10-21'),(10,'2019-10-22');
INSERT 0 10
postgres=# WITH
  first_stored AS (SELECT min(stored_date) as first FROM my_table WHERE stored_date > DATE_TRUNC('WEEK', '2019-10-26'::date) - INTERVAL '8 DAY'),
  last_stored AS (SELECT max(stored_date) as last FROM my_table WHERE stored_date < DATE_TRUNC('WEEK', '2019-10-26'::date) - INTERVAL '1 DAY')
SELECT id, stored_date FROM my_table mt, first_stored fs, last_stored ls WHERE mt.stored_date in (fs.first,ls.last) ORDER BY stored_date;
 id | stored_date 
----+-------------
  6 | 2019-10-15
  8 | 2019-10-18
(2 rows)

Disclosure: I work for EnterpriseDB (EDB)

Answer 2

You might want something like this (see fiddle):

SELECT DATE_TRUNC('WEEK', NOW()) - INTERVAL '8 DAY' AS first_day_of_last_week, 
       DATE_TRUNC('WEEK', NOW()) - INTERVAL '1 DAY' AS  last_day_of_last_week;

Result:

first_day_of_last_week  last_day_of_last_week
2019-10-13 00:00:00+01  2019-10-20 00:00:00+01