get data from last week monday to last week sunday in postgresql-CodePudding

I have a request which must be formulated as follows in postgresql:

SELECT AVG(voltage) FROM TABLE1,TABLE2
WHERE TABLE1.id=TABLE2.table1_id AND recharged BETWEEN last_week_monday AND last_week_sunday
GROUP BY TABLE1.name

I want to know how to get last_week_monday and last_week_sunday

CodePudding user response：

One option uses DATE_TRUNC:

SELECT AVG(voltage)
FROM TABLE1 t1
INNER JOIN TABLE2 t2
    ON t1.id = t2.table1_id
WHERE recharged >= DATE_TRUNC('week', NOW()) - interval '7 day' AND
      recharged < DATE_TRUNC('week', NOW());

Postgres' DATE_TRUNC() function treats Monday as the start of the week.