I am doing this request in phpmyadmin

SELECT * FROM `ads` WHERE (name <> "tmp" AND ads.reg_date >= "2016-06-20" AND ads.reg_date <= "2016-06-20") AND (`user_id`='126')

returns void however if I make such a request

 SELECT * FROM `ads` WHERE (name <> "tmp" AND ads.reg_date >= "2016-06-20" AND ads.reg_date < "2016-06-21") AND (`user_id`='126')

then one entry is excellent ... What's the matter, gentlemen?

  • Not playable. Any other criteria don't work exactly? What type of reg_date field? - cheops
  • reg_date type timestamp - Anatoly
  • reg_date matters 2016-06-20 00:00:00 ? It is exactly by midnight that the date string is given without time when compared with the timestamp - the date with time. - Fine
  • 2016-06-20 06:29:32 like this - Anatoly

3 answers 3

Another option to consider the time frame, namely: SELECT * FROM ads WHERE (name <> "tmp" AND ads.reg_date> = "2016-06-20 00:00:00" AND ads.reg_date <= "2016-06 -20 23:59:59 ") AND ( user_id = '126')

  • I used your option. Works. - Anatoly
  • I rechecked the last second of the day in mysql - always 11:59:59 pm, there may simply be several of them. A value of 23:59:60 is not possible, so the answer is correct. dev.mysql.com/doc/refman/5.7/en/time-zone-leap-seconds.html Just in case, let me remind you that in a minute there is not always 60 seconds. Happens and 61. - Small

According to the results of clarifications in the comments.

reg_date has a timestamp data type. This means that the reg_date >= :foo AND reg_date <= :foo condition reg_date >= :foo AND reg_date <= :foo only if the entire value and time and date match. And it is not the string "2016-06-20" that is compared, but the string "2016-06-20" after the cast to the timestamp . It is the string that is brought to the timestamp, and not the timestamp is truncated to the date and compared with the string. Date without time to the timestamp given with the time of midnight, i.e. condition:

 ads.reg_date >= "2016-06-20" AND ads.reg_date <= "2016-06-20"

The scheduler treats as

 ads.reg_date >= "2016-06-20 00:00:00" AND ads.reg_date <= "2016-06-20 00:00:00"

You, apparently, do not have such a value in the table. It is often more convenient to write this way than to get the date of the next day on the application:

 ads.reg_date >= "2016-06-20" AND ads.reg_date < "2016-06-20" + interval 1 day

    "2016-06-20" - recording of this format is reduced to the record "2016-06-20 00:00:00". It turns out that the only date that satisfies your request is "2016-06-20 00:00:00". Try this:

     SELECT * FROM `ads` WHERE (name <> "tmp" AND DATE(ads.reg_date) >= "2016-06-20" AND DATE(ads.reg_date) <= "2016-06-20") AND (`user_id`='126')

    DATE () - cuts time, leaving only the date.

    • This request works. The problem is how it works. Addressing the date(ads.reg_date) excludes the use of the reg_date index. - Minor