通用
通用
SELECT EXTRACT(YEAR FROM o.created_at) AS y,
EXTRACT(MONTH FROM o.created_at) AS m,
EXTRACT(DAY FROM o.created_at) AS d
FROM orders o;MySQL 简写 / PostgreSQL 扩展
MySQL 简写 / PostgreSQL 扩展
-- MySQL
SELECT YEAR(created_at), MONTH(created_at), DAYOFMONTH(created_at) FROM orders;
-- PostgreSQL
SELECT EXTRACT(QUARTER FROM ts), EXTRACT(ISODOW FROM ts), EXTRACT(EPOCH FROM ts) FROM t;| 单位 | YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/QUARTER/WEEK |
|---|---|
| PostgreSQL | 还支持 DOW/ISODOW/DOY/EPOCH；返回 numeric |
| MySQL | EXTRACT 之外可用 YEAR()/MONTH()/DAY()/HOUR() 等函数 |