0

I needed to know how many users registered during June and July, this is the first query I wrote:

select count(*) from users where created_at>="2013-06-01" and created_at<="2013-07-31"

Result: 15,982

select count(*) from users where year(created_at)=2013 and month(created_at) in (6,7)

Result: 16,278

Why do they return a different result? Could someone explain? Or am I missing something?

Thanks.

miligraf
  • 1,042
  • 9
  • 22
  • What is the type of the created_at column? If it happens to be a char based column instead of a date column it may not compare as expected. – Deadron Aug 09 '13 at 17:03

2 Answers2

2

Both query should be equivalent, except that the first one is able to make use of an index and it should be faster, and except the case in which created_at is not a DATE but is a TIMESTAMP.

If created_at is a timestamp, you should write your first query this way:

select count(*) from users
where created_at>='2013-06-01' and created_at<'2013-08-01'

otherwise your first query will exclude all records created on 31th of July, after midnight, eg. 2013-07-31 00:00:00 will be included while 2013-07-31 09:15:43 will be not.

fthiella
  • 48,073
  • 15
  • 90
  • 106
1

The reason is that your date values do not include the last day: The date constants are converted to a timestamp at midnight. You are querying between these values:

2013-06-01 00:00:00
2013-07-31 00:00:00

So only the first second of the last day is included.

Try this:

select count(*)
from users
where created_at>="2013-06-01"
and created_at<="2013-07-31 23:59:59"

Or more simply make less than the next day:

select count(*)
from users
where created_at>="2013-06-01"
and created_at<"2013-08-01" -- < 1st day of next month
Bohemian
  • 412,405
  • 93
  • 575
  • 722