Clever Geek Handbook

What's the difference between

I have my first request

select count(*)
from `order`
where marketer_id = 75 and
      HandleStatus != -1 and
      (Created_at BETWEEN '2017-05-01' AND '2017-05-31')

and the result is 1050

I also have a second request:

select count(*)
from `order`
where marketer_id = 75 and
      HandleStatus != -1 and
      (Month(Created_at) =5 and Year(Created_at) = 2017)

and the result is 1111

I think 2 queries have the same meaning, but it returns 2 different results. Column information "Created_at": COLUMN_NAME Created_at, COLUMN_TYPE timestamp, IS_NULLABLE NO, COLUMN_KEY, COLUMN_DEFAULT CURRENT_TIMESTAMP

Please help, what's the difference between the two queries?

+3


source to share


3 answers


If you count the time for a day, the first query only returns results up to 2017-05-31 00:00:00. If you have any results after 2017-05-31 00:00:00 and before 2017-05-31 23:59:59 (possibly up to milliseconds too), they only show up in the second query.



+6


source


The first request does not look at May 31, it only looks until May 30. The absence of a time component means that the time is taken as midnight or the beginning of the 31st.



+1


source


Between only runs before '2017-05-30 23:59:59' and after 2017-05-01 00:00:00. It does not have to count the 31st full day.

If you want the first query to return the same data as the second query, you can use this like

select count(*)
from `order`
where marketer_id = 75 and
      HandleStatus != -1 and
      (Created_at >= '2017-05-01' AND Created_at < '2017-06-01')
0


source






More articles:


All Articles