将Timestamp转换为在WHERE中可用查询的MYSQL日期

Question

我正在使用一个MYSQL数据库，其中开发人员已将日期存储为时间戳，而不是可以查询的“日期时间”字段。

下面的查询创建了一个'AS‘字段date_formatted，并且输出是正确的。

但是，不能在WHERE语句中使用该字段来按日期限制它

按照日期范围限制时间戳的正确语法是什么？

SELECT *,DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y-%m-%d %H:%i:%s') AS 'date_formatted' 
FROM `table` 
where 'date_formatted' >= '20210801' 

where语句返回今年的所有结果，换句话说，格式化的日期不会被视为日期时间字段，而是在where语句中使用时被视为字符串。

谢谢!

请参阅[Convert Timstamp to MYSQL Date](https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query)

Answer 1

一种方法是使用having

SELECT t.*,
       DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y-%m-%d %H:%i:%s') AS date_formatted
FROM `table` t
HAVING date_formatted >= '20210801' ;

但是，最好将其表述为：

SELECT t.*,
       DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y-%m-%d %H:%i:%s') AS date_formatted
FROM `table` t
WHERE timestamp >= UNIX_TIMESTAMP('2021-08-01');

此优化器利用timestamp列上的索引和统计信息。

Answer 2

不能在where子句中使用该字段，因为要作为输出获取的列不是表的一部分。因此，将一个新列作为date_formatted添加到表中。

ALTER TABLE table 
ADD date_formatted date;

UPDATE table 
SET date_formatted = DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y-%m-%d %H:%i:%s') 

现在，在下一步中，您可以在WHERE子句中使用date_formatted。