Jump to content

Timestamp Error


lukelambert

Recommended Posts

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.
Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.