Timestamp Error

[lukelambert]

I wrote a script to display the days of a selected month in calendar form (4, 5 or 6 rows by 7 columns). I wanted the days of the previous and next months to fill the empty cells on the calendar before and after the days in the selected month.

For example, the calendar for November 2006 would look like this:
[table]
[tr]
[td]Sun[/td]
[td]Mon[/td]
[td]Tue[/td]
[td]Wed[/td]
[td]Thu[/td]
[td]Fri[/td]
[td]Sat[/td]
[/tr]
[tr]
[td][color=gray]29[/color][/td]
[td][color=gray]30[/color][/td]
[td][color=gray]31[/color][/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]4[/td]
[/tr]
[tr]
[td]5[/td]
[td]6[/td]
[td]7[/td]
[td]8[/td]
[td]9[/td]
[td]10[/td]
[td]11[/td]
[/tr]
[tr]
[td]12[/td]
[td]13[/td]
[td]14[/td]
[td]15[/td]
[td]16[/td]
[td]17[/td]
[td]18[/td]
[/tr]
[tr]
[td]19[/td]
[td]20[/td]
[td]21[/td]
[td]22[/td]
[td]23[/td]
[td]24[/td]
[td]25[/td]
[/tr]
[tr]
[td]26[/td]
[td]27[/td]
[td]28[/td]
[td]29[/td]
[td]30[/td]
[td][color=gray]1[/color][/td]
[td][color=gray]2[/color][/td]
[/tr]
[/table]

The display part is working perfectly but now I want to select events from an SQL table that have a timestamp that is greater than or equal to the first day on the calendar at 12:00 am and less than one day after the last day on the calendar at 12:00 am. In other words, I want the events that take place between the beginning and end of the calendar.

Anyway, here is my code...
[code]
$month = 11; // Let's say the selected month is November 2006
$year = 2006;

$month_days = array( // The number of days in each month
    1 => 31,
    2 => 28,
    3 => 31,
    4 => 30,
    5 => 31,
    6 => 30,
    7 => 31,
    8 => 31,
    9 => 30,
    10 => 31,
    11 => 30,
    12 => 31
);

if (date("L", mktime(0, 0, 0, 1, 1, $year))) // Determines if it is a leap year
    $month_days[2] = 29;

$day_of_week = date("w", mktime(0, 0, 0, $month, 1, $year)); // The day of the week of the first day of the month

$num_rows = ceil(($month_days[$month] + $day_of_week) / 7); // Number of seven cell rows

$timestamp_begin = date("U", mktime(0, 0, 0, $month, 1, $year)) - $day_of_week * 86400;
/* The timestamp of the first day of the month minus (the number of
days before the month begins times the number of seconds in a day) */

$timestamp_end = $timestamp_begin + $num_rows * 7 * 86400;
/* The timestamp of the first day on the calendar plus (the
number of cells times the number of seconds in a day) */

echo $timestamp_begin . " = " . date("F d, Y H:i:s", $timestamp_begin) . "<br />";
echo $timestamp_end . " = " . date("F d, Y H:i:s", $timestamp_end);
[/code]

This prints
[code]
1162101600 = October 29, 2006 01:00:00
1165125600 = December 03, 2006 00:00:00
[/code]

On some months it works perfectly and on others it is an hour off on either $timestamp_begin or $timestamp_end. Am I doing something wrong or is this a bug?

I am running PHP Version 5.1.2 on Mac OS X 10.4.7.

[448191]

You're overcomplicating things. You want to query a table for all entries modified in november?

SELECT * FROM table WHERE timestamp BETWEEN '20061101000000' AND '20061201000000'

[lukelambert]

I don't want events only in November. I want from October 29 to December 2. Yes I could SELECT * FROM table WHERE timestamp BETWEEN '20061029000000' AND '20061203000000' but the 'timestamp' column is int(11).

[448191]

Pfffff. It doesn't matter. The point is; you don't need all these time conversion functions.

Why is your 'timestamp' an INT datatype col, when it's appropiate to store dates in either DATE, DATETIME or TIMESTAMP type cols?

I still believe your making this far more complicated than it needs to be.

SELECT * FROM table WHERE timestamp BETWEEN '20061002000000' AND '20061202000000'

or if col of DATE type;

SELECT * FROM table WHERE timestamp BETWEEN '20061002 AND '20061202'

[lukelambert]

The table was created by a third party product. It is already populated with data and I don't intend on altering it. Yes, I agree that your solution would by far be the easiest way, but I am not looking for a new solution. I need to know why my code is not working. If there are 86400 seconds in one day and 3024000 in 35 days shouldn't that math work out?