Last day of previous month appears as first day of current month?

[JAB Creations]

For some unknown reason the last day of previous month appears as first day of current month in my statistics script. Now there are a lot of different things that could be triggering this (and I don't think this issue occurs all the time too) so I'll try to include all the variables I think are relevant to make figuring this out as simple as possible...

1.) I use the following MySQL query to set the time zone before any other queries...

SET time_zone := '+00:00'

2.) The date part of the statistics queries look like this...

WHERE date>'2011-01-01 00:00:00' AND date<'2011-02-01 00:00:00'

3.) In the PHP code that sets the date I am using the following code...

<?php
$m1 = date(n);
$y1 = date(o);
?>

I've used Y for the date/year before and after changing it didn't notice any changes (the error still exists). I'm far from mastering date/time so a little insight would be greatly appreciated. :)

- John

[jadebox]

I'm not sure it's the cause of the problem you are seeing, but ... If you want all the records where date is in 2011-01 then ....

WHERE date > '2011-01-01 00:00:00' AND date < '2011-02-01 00:00:00'

... probably should be:

WHERE date >= '2011-01-01 00:00:00' AND date < '2011-02-01 00:00:00'

or

WHERE date LIKE '2011-01%'

-- Roger

[jadebox]

In my previous reply, I was thinking you were working with just the date, not the date and time. So, I thought that you were missing all the records for the first day of the month. But, actually you would only miss ones for the first minute. So, that's probably not the problem you are seeing (though you probably should still make the change I suggested).

Did that make any sense? :-)

If you are seeing the reports as being off by one day, the problem may be where you are storing or displaying the results, rather than in the query. If you could post more complete sections of real code, it would make it easier for us to help.

-- Roger