What difference between

Question

I have first query

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

and result is 1050

i also have second query :

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

and result is 1111

I think 2 query have same meaning but it return 2 different result. Info about column "Created_at": COLUMN_NAME Created_at, COLUMN_TYPE timestamp, IS_NULLABLE NO, COLUMN_KEY , COLUMN_DEFAULT CURRENT_TIMESTAMP

Please help what difference between 2 query?

Answer 1

If you consider the time within a day, the first query only returns results before 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 (maybe down to milliseconds too), they only show up in the second query.

Answer 2

The first query is not looking at 31st May, it looks only until 30 May. The absence of a time component means the time is taken as midnight, or the start of the 31st.