[SOLVED] Date queries not adding up

[lemmin]

I am trying to query a DB for the number of rows that exist where a date field is between two values.

 

Here is the code:

$query = "SELECT COUNT(*) as ct FROM users WHERE registrationDate >= '2008-07-01' AND registrationDate <= '2008-09-30' AND userType = 3";
$result = mysql_query($query) or die (mysql_error());
echo mysql_result($result, 0) . "<br>";

$query = "SELECT COUNT(*) as ct FROM users WHERE registrationDate >= '2008-10-01' AND registrationDate <= '2008-12-31' AND userType = 3";
$result = mysql_query($query) or die (mysql_error());
echo mysql_result($result, 0) . "<br>";

$query = "SELECT COUNT(*) as ct FROM users WHERE registrationDate >= '2009-01-01' AND registrationDate <= '2009-03-31' AND userType = 3";
$result = mysql_query($query) or die (mysql_error());
echo mysql_result($result, 0) . "<br>";

$query = "SELECT COUNT(*) as ct FROM users WHERE registrationDate >= '2009-04-01' AND registrationDate <= '2009-06-30' AND userType = 3";
$result = mysql_query($query) or die (mysql_error());
echo mysql_result($result, 0) . "<br>";

$query = "SELECT COUNT(*) as ct FROM users WHERE registrationDate >= '2008-07-01' AND registrationDate <= '2009-06-30' AND userType = 3";
$result = mysql_query($query) or die (mysql_error());
echo mysql_result($result, 0);

 

Here is the output:

98

261

303

195

878

 

The first four queries are supposed to be grabbing the numbers for each quarter of the year and the last one grabs the total. The last query returns 878, but when you add up the first four numbers the total is 857.

 

Does anyone have any idea why the numbers aren't the same?

 

Thank you.

[gizmola]

I have a theory.  What is the type of the registrationDate column?  Is it a DATETIME or TIMESTAMP?  If so, there is your answer -- you have registrations that occurred in the last 23 hours and 59 seconds of the last day of the month.

[lemmin]

You were right!

 

It is a TIMESTAMP type. I changed the queries to include times between 00:00:00 and 23:59:59 and the numbers matched up.

 

Thanks for the help!