postgresql - 在postgresql中获取上周周一到上周周日的数据

Question

我有一个请求，必须在 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

我想知道如何获得 last_week_monday 和 last_week_sunday

Answer 1

一个选项使用 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() 函数将星期一视为一周的开始。