1

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

Viktor
  • 517
  • 5
  • 23

2 Answers2

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Thank you, Gordon. It's the occam's razor. The simplest solution being the most elegant and the elusive obvious. The most efficient way is to keep the timestamp as is and put the where clause instead of working from a datetime. Thank you again. – Viktor Aug 02 '21 at 01:08
0

The field cannot be used in the where clause because column that you are getting as a output is not part of the table. So add a new column to the table as 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')

Now in the next step you can use date_formatted in the WHERE clause.

Sanyogita Waykar
  • 58
  • 4
  • this solution won't work because the table keeps having new records - the update would only update the current records in the table. Thanks for your answer however, the above answer from Gordon works dynamically. We've added an index on timestamp so its fast and in real time. – Viktor Aug 02 '21 at 01:01