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?

Run this query `select count(*) from order where marketer_id = 75 and HandleStatus != -1 and (Month(Created_at) =5 and Year(Created_at) = 2017) and NOT (Created_at BETWEEN '2017-05-01' AND '2017-05-31')` and see which rows are returned. My guess is that there are rows with times (not just the date) in the timestamp. — Turophile, Jun 05 '17 at 05:04
Learn something new every day here [**demo**](http://rextester.com/DORB47261). The first query uses May 31st at midnight, excluding the majority of that day. — Tim Biegeleisen, Jun 05 '17 at 05:10
@TimBiegeleisen Here is a variation of your demo: http://rextester.com/DORB47261 — Turophile, Jun 05 '17 at 05:16

score 7 · Answer 1 · answered Jun 05 '17 at 05:04

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.

score 2 · Answer 2 · answered Jun 05 '17 at 05:05

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.

answered Jun 05 '17 at 05:05

Phylyp

1,659
13
16

Nayas Subramanian · Answer 3 · 2017-06-05T08:27:20.643

1

Between does take only up to '2017-05-30 23:59:59' and after 2017-05-01 00:00:00. It should not consider 31th full day.

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

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

edited Jun 05 '17 at 08:27

answered Jun 05 '17 at 05:15

Nayas Subramanian

2,269
21
28

What difference between

3 Answers3