Search code examples
mysqlsqltimestampdate-format

Convert Timestamp to MYSQL Date In Query usable in WHERE

I am working with a MYSQL database where the developer has stored the date as a timestamp instead of a 'datetime' field that can be queried.

The following query creates an 'AS' field date_formatted and the output is correct.

However that field cannot be used in a WHERE statement to limit it by date

What would be the correct syntax to limit the timestamp by a date range?

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

The where statement returns all the results of this year in other words the formatted date is not being treated as a datetime field it is being treated as a string when used in a where statement.

Thanks!

see [Convert Timstamp to MYSQL Date]1

Solution

  • One method is to use having:

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

    However, it is better to phrase this as:

    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');

    This optimizer take advantage of indexes and statistics on the timestamp column.