1

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;
  • 1
    What is the data type of date_created ??? – RolandoMySQLDBA Nov 18 '18 at 17:53
  • @RolandoMySQLDBA int – DojoDev Nov 18 '18 at 18:29
  • How many rows in the result? Please provide SHOW CREATE TABLE evaluation_client . – Rick James Nov 19 '18 at 17:42
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.

  • 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";` – DojoDev Nov 18 '18 at 18:25
  • @DojoDev - I was wrong. Just add quotes around 2018-11-11. (Fixed my Answer.) – Rick James Nov 18 '18 at 19:54
  • 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 Nov 19 '18 at 6:20
  • check my updated question please. – DojoDev Nov 19 '18 at 7:02
  • 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 .... – Rick James Nov 20 '18 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.