示例
示例
-- MySQL
SELECT DATE(created_at) AS day, COUNT(*) AS cnt
FROM orders
WHERE created_at >= '2024-06-01'
AND created_at < '2024-07-01'
GROUP BY DATE(created_at)
ORDER BY day;PostgreSQL 按周截断
PostgreSQL 按周截断
SELECT date_trunc('week', created_at) AS wk, COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 1;| MySQL | DATE() 取天；YEAR_MONTH? 用 DATE_FORMAT 自定义 |
|---|---|
| PostgreSQL | date_trunc 支持 week/quarter 等粒度 |
| 过滤 | 范围条件写在原始时间列上，利于走索引 |