MySQL将Unix时间转日期后按日历日期范围查询数据异常求助

嘿，我来帮你搞定这个查询问题！你现在遇到的情况大概率是写法细节没处理好，或者没考虑到性能和精度的问题，咱们一步步捋清楚。

先说说你原语句的潜在问题 #

你写的SELECT * FROM tbl_user WHERE DATE(FROM_UNIXTIME(registeredOn)) between '2018-03-17' AND '2018-03-17'有两个容易踩坑的点：

• 索引失效：当你用DATE(FROM_UNIXTIME(registeredOn))这种函数包裹字段时，MySQL没办法使用registeredOn上的索引（如果有的话），数据量大的时候会触发全表扫描，不仅慢，还可能因为扫描逻辑导致结果异常。
• 时间戳精度问题：如果你的registeredOn是毫秒级的13位时间戳（比如1521254399000），直接用FROM_UNIXTIME会转换出错误的日期（它默认处理秒级的10位时间戳），这也会导致查询结果不对。

推荐的正确写法（高效且准确） #

最优解是把日期范围转换成Unix时间戳，这样能直接利用registeredOn的索引，速度快还精准：

比如要查询2018-03-17到2018-03-20的所有用户记录，你可以这么写：

SELECT * FROM `tbl_user` 
WHERE registeredOn >= UNIX_TIMESTAMP('2018-03-17') 
  AND registeredOn < UNIX_TIMESTAMP('2018-03-21');

• UNIX_TIMESTAMP('2018-03-17')会返回当天00:00:00的秒级时间戳
• UNIX_TIMESTAMP('2018-03-21')是次日00:00:00的时间戳，用<而不是<=可以完美包含2018-03-20当天的所有时间（包括23:59:59），不会漏掉也不会多算下一天的0点记录。

如果你的时间戳是毫秒级的，记得乘以1000对齐精度：

SELECT * FROM `tbl_user` 
WHERE registeredOn >= UNIX_TIMESTAMP('2018-03-17') * 1000 
  AND registeredOn < UNIX_TIMESTAMP('2018-03-21') * 1000;

如果你非要用日期函数转换（不推荐，效率低） #

如果因为某些场景必须把时间戳转成日期来查询，那正确的写法是：

SELECT * FROM `tbl_user` 
WHERE DATE(FROM_UNIXTIME(registeredOn)) BETWEEN '2018-03-17' AND '2018-03-20';

或者更精确地指定时分秒：

SELECT * FROM `tbl_user` 
WHERE FROM_UNIXTIME(registeredOn) BETWEEN '2018-03-17 00:00:00' AND '2018-03-20 23:59:59';

但再次强调，这种写法会触发全表扫描，数据量大的时候性能很差，尽量优先用第一种方法。

最后检查下你的时间戳类型 #

先确认下registeredOn字段的时间戳是秒级（10位）还是毫秒级（13位），可以用SELECT FROM_UNIXTIME(registeredOn)或者SELECT FROM_UNIXTIME(registeredOn/1000)看看转换后的日期是否正确，这是很多人踩坑的关键！

内容的提问来源于stack exchange，提问作者Mangal OneVoice