MySQL – How to Fix Date Query Not Working Issues

datedate formatdatetimeMySQLmysqli

I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute.

I checked my DataBase where date is saved in 1446976737 format which seems unix timestamp. BUT in my filters user is entering human readable dates such as 2018-11-11. Now by executing the below query i'm not getting result. Is there anything wrong with my query?

SELECT  `e`.`id` AS `id`, `e`.`first_name` AS `first_name`,
        `e`.`gender` AS `gender`,
        `e`.`email` AS `email`, `e`.`phone` AS `phone`, `e`.`age` AS `age`,
        `e`.`version` AS `version`, `e`.`evaluation_status` AS `evaluation_status`,
        `e`.`ip_address` AS `ip_address`, `e`.`date_created` AS `date_created`,
        `e`.`date_updated` AS `date_updated`
    FROM  `evaluation_client` AS `e`
    WHERE
       AND  `e`.`date_created` >= 2018-11-11
       AND  `e`.`date_created` <= 2018-11-18;

From the answers and comment i also tried the below query but still it's taking 3 minutes to load the data is it normal? How can i make it faster?

SELECT  `e`.`id` AS `id`, `e`.`first_name` AS `first_name`,
        `e`.`gender` AS `gender`,
        `e`.`email` AS `email`, `e`.`phone` AS `phone`, `e`.`age` AS `age`,
        `e`.`version` AS `version`, `e`.`evaluation_status` AS `evaluation_status`,
        `e`.`ip_address` AS `ip_address`, `e`.`date_created` AS `date_created`,
        `e`.`date_updated` AS `date_updated`
    FROM  `evaluation_client` AS `e`
    WHERE
        `e`.`date_created` >= unix_timestamp("2015-11-11")
       AND  `e`.`date_created` <= unix_timestamp("2015-11-19")

    ORDER BY
       `e`.`date_created` DESC;

Best Answer

(This is why we asked for the datatype of date_created!)

Assuming date_created is a TIMESTAMP:

2018-11-11 is an arithmetic expression that evaluates to 1996; certainly not what you wanted.

"2018-11-11" (with quotes) will compare correctly to 1446976737.

UNIX_TIMESTAMP(ts_column) will generate something like 1446976737, so it should work correctly. But don't "hide a column in a function".

As for

`e`.`date_created` AS `date_created`

Since the alias (AS...) does not do anything extra, leave it off. I am worried that 

AND  `e`.`date_created`

will look at the alias, not the value. It needs to look at the value to use this:

INDEX(date_created)

Do you have that index?

By using >= and <= you are including midnight from both ends; did you intend to do that?

WHERE AND is syntactically incorrect. Did you leave some stuff out? It may be important. If you have

WHERE x = 1
  AND date_created ...

then a better index would be

INDEX(x, date_created)

1446976737 dates back a few years:

mysql> SELECT FROM_UNIXTIME(1446976737), UNIX_TIMESTAMP("2018-11-11");
+---------------------------+------------------------------+
| FROM_UNIXTIME(1446976737) | UNIX_TIMESTAMP("2018-11-11") |
+---------------------------+------------------------------+
| 2015-11-08 01:58:57       |                   1541923200 |
+---------------------------+------------------------------+

When you can't figure out what to do, here's a hint: See what you get from SELECT * FROM tbl. Then put quotes around something in that format. In the case of a TIMESTAMP column, you will get something like

2015-11-08 01:58:57

So, you need date_created = "2015-11-08 01:58:57"

Assuming date_created is INT:

Either of these is a valid way to compare:

date_created >= 1446976737
date_created >= UNIX_TIMESTAMP("2015-11-08 01:58:57")

It is still valid, and wise, to have the index(es) mentioned above.

Related Question