The extract function seems useful and the date function I have found solves some of my problems, but is there any way to replicate PostgreSQL’s date_trunc?
事实上,EXTRACT看起来像是这个具体情况最接近的比赛.
您在PG中的原始代码:
WHERE date_trunc('month', QUERY_DATE) BETWEEN
date_trunc('month', now()) - INTERVAL '4 MONTH' AND
date_trunc('month', now() - INTERVAL '1 WEEK')
使用EXTRACT:
WHERE EXTRACT(YEAR_MONTH FROM QUERY_DATE)
BETWEEN
EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 4 MONTH)
AND
EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 WEEK)
虽然它在功能上是相同的,但是在进行比较之前,这实际上会将日期变成YYYYMM字符串.
另一个选择是使用DATE_FORMAT重建日期字符串并强制到月初:
WHERE DATE_FORMAT(QUERY_DATE, '%Y-%m-01')
BETWEEN
DATE_FORMAT(NOW() - INTERVAL 4 MONTH, '%Y-%m-01')
AND
DATE_FORMAT(NOW() - INTERVAL 1 WEEK, '%Y-%m-01')
另外,请注意,MySQL在处理日期范围方面真的很差,即使在字段被索引时也是如此.如果您不小心,您可能会完成全表扫描.