Searching through dates query not working?

Question

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;

• RolandoMySQLDBA: What is the data type of date_created ???
• DojoDev: @RolandoMySQLDBA int
• Rick James: How many rows in the result? Please provide SHOW CREATE TABLE evaluation_client .

Answer 1

(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.

• DojoDev: I tried all of the above tricks none worked. I even tried the below 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, e.date_updated AS date_updated FROM evaluation_client AS e WHERE e.date_created = "1446976737";`
• Rick James: @DojoDev - I was wrong. Just add quotes around 2018年11月11日. (Fixed my Answer.)
• DojoDev: in table we have entries for dates like 1446976737 which is unix timestamp and datatype for these fields is int and I'm comparing input like 2015年11月08日 so putting quotes around "2015年11月08日" will compare correctly?
• DojoDev: check my updated question please.
• Rick James: Grrr... I needed to know that it was INT instead of TIMESTAMP. See my update. If there are still issues, please provide a current SHOW CREATE TABLE, and EXPLAIN SELECT ....