[SOLVED] DATE expert help needed (PHP and MySQL DATE Comparison)

[kjtocool]

**Please Note: The code is 100% functional and working except for the comparison issue described below.**

 

Problem I am trying to compare two dates: today's date and the prediction deadline (Each Friday at 4:01 am ET).  The comparison works totally perfectly throughout the week, but each Friday at 12:00 am ET, it falsely says that the deadline has passed (when it should stay open another 4 hours until 4:01 am ET).  The value is stored in a MySQL database as a datetime variable.

 

 

There is an admin section where I add a new 'week'.  Each week is stored in a MySQL database, and each week has a 'Prediction Deadline' column.  Here is how I set that date:

 

<?php
// There is a form allowing the user to specify the date, it comes across in the following format: 2008-01-18, or, YEAR-MM-DD
$prediction_end = $_POST['prediction_end'];
// I then append a time to the end of this date, creating the format: 2008-01-18 4:1:00, or, YEAR-MM-DD H:M:SS
$prediction_end .= " 4:1:00";

... other code ...

//Here is the query to add a new week to the database, including the $prediction_end column
$query = "INSERT INTO bonanza_weeks VALUES (NULL, '$prediction_end', '$weekend_start', '$weekend_end', '$movie_1_name', $movie_1_opener, '$movie_2_name', $movie_2_opener, '$movie_3_name', $movie_3_opener, '$movie_4_name', $movie_4_opener, '$movie_5_name', $movie_5_opener, '$movie_6_name', $movie_6_opener, '$movie_7_name', $movie_7_opener, '$movie_8_name', $movie_8_opener, '$movie_9_name', $movie_9_opener, '$movie_10_name', $movie_10_opener)";
// The above query works fine, and the week is properly added to the database.  The date looks like this in the database: 2008-01-18 04:01:00
// The prediction_deadline column in the database is of type datetime

... other code ...
?>

 

You may be asking yourself, why not use: $prediction_end .= " 04:01:00"; ...  I have, it resulted in the same problem.

 

So now the week is added to the database, and the prediction deadline stored as a datetime variable.  I next have a page where I do a comparison, here is the relevant code:

 

<?php
// Here I generate today's date, which I will compare to the value in the database
$today = getdate();
$today_year = $today['year'];
$today_month = $today['mon'];
$today_month = str_pad($today_month, 2, "0", STR_PAD_LEFT);
$today_day = $today['mday'];
$today_day = str_pad($today_day, 2, "0", STR_PAD_LEFT);
$today_hours = $today['hours'];
$today_minutes = $today['minutes'];
$today_seconds = $today['seconds'];
$today_date = $today_year . "-" . $today_month . "-" . $today_day . " " . $today_hours . ":" . $today_minutes . ":" . $today_seconds;
// The following echo returns: 2008-01-18 12:24:30
echo $today_date . "<br />";

... some code ...												

// I get the deadline value from the database
$latest_prediction_deadline = $row['prediction_end'];
// The following echo returns: 2008-01-18 04:01:00
echo $latest_prediction_deadline . "<br />";

// Here is a comparison I did, trying to subtract one date from another.  It echoed "0"
$some = $today_date - $latest_prediction_deadline;
echo $some . "<br />";

... code ...

// Here is the comparison:
if ($today_date < $latest_prediction_deadline) {
     more code
}
?>

 

 

Now, the comparison normally works fine, but seems to only compare correctly for the date, and not the time.  I don't understand what I'm doing wrong, could it have something to do with the way I am building today's date?  The way I append the time onto the POST value of prediction deadline?

 

I'm really lost.  ???

[cooldude832]

did u check what time zone your server is set to

12 am you  = 5 am server for example

[p2grace]

Without looking at your code, the easiest way to compare dates is to use a linux timestamp saving it into the database as an integer.  Then converting the timestamp to a date like this:

 

// orig timestamp
$timestamp = time();

// converted timestamp
$date = date("Y-m-d H:m:s",$timestamp);

[kjtocool]

did u check what time zone your server is set to

12 am you  = 5 am server for example

 

Yes, it's at ET.  Getting Today's Date would obviously get the server time of the page load, and it's showing up ET.

[kjtocool]

Without looking at your code, the easiest way to compare dates is to use a linux timestamp saving it into the database as an integer.  Then converting the timestamp to a date like this:

 

// orig timestamp
$timestamp = time();

// converted timestamp
$date = date("Y-m-d H:m:s",$timestamp);

 

So instead of getDate, you think I should use time()?

 

That might make the code simpler, but is there any fundamental difference that would change the outcome?

[p2grace]

The fundamental difference would be the ability to use a unix timestamp and convert it to whatever format you wish using the date function.  It also makes for comparing dates much easier.

[kjtocool]

I just tried and echoed the code you gave, it fed back:

 

timestamp: 2008-01-18 12:01:32

 

It seems you duplicated m twice, so the month is showing up as minutes. 

 

Even if fixed, it still is in the same format of the timestamp I crated using getDate(), so I don't see how that would solve the problem.  I personally believe something is wrong with the comparison itself, because I think that the time isn't being stored properly for some reason in the database. 

 

I wonder if it's maybe because of this code I use:

 

// There is a form allowing the user to specify the date, it comes across in the following format: 2008-01-18, or, YEAR-MM-DD
$prediction_end = $_POST['prediction_end'];
// I then append a time to the end of this date, creating the format: 2008-01-18 4:1:00, or, YEAR-MM-DD H:M:SS
$prediction_end .= " 4:1:00";

[p2grace]

They have to be two digits.

 

// There is a form allowing the user to specify the date, it comes across in the following format: 2008-01-18, or, YEAR-MM-DD
$prediction_end = $_POST['prediction_end'];
// I then append a time to the end of this date, creating the format: 2008-01-18 4:1:00, or, YEAR-MM-DD H:M:SS
$prediction_end .= " 04:01:00";

[kjtocool]

Heh,

 

As I said above, I have tried that as well, it resulted in the same problem.  They really don't have to be two digits, when MySQL stores it as a datetime, it auto appends the 0's.

[p2grace]

If you use time time() function it should work because you can put it in whatever format you want.  If you echo time() you should see something like 1200677782.  Then you can convert that timestamp into whatever format you wish using the date function, so you can compare whatever values you want in whichever format you want.

[kjtocool]

Sigh, I just don't understand why you think the time stamp will make a lick of difference.  Sure, I can format it however I want, but I can do the same thing with getDate(), albeit with a bit more work.

 

I'll go ahead and make the modification, but it's only going to effect the 'Today's Date' variable.  I can't add a timestamp for Prediction deadline, since it's a date in the future.  I still strongly believe the problem is related to me appending a string to the date, then storing it as datetime in MySQL, not with the formating associated with getDate(), which works fine.

 

How do I fix the timestamp so that it shows minutes not months like it currently does.  In the example you gave, you used months (m) twice.

[p2grace]

Alright, well hold off for one second and let me take a deeper look into your code.

[p2grace]

Try this:

 

<?php
// Here I generate today's date, which I will compare to the value in the database
$today = getdate();
$today_year = $today['year'];
$today_month = $today['mon'];
$today_month = str_pad($today_month, 2, "0", STR_PAD_LEFT);
$today_day = $today['mday'];
$today_day = str_pad($today_day, 2, "0", STR_PAD_LEFT);
$today_hours = $today['hours'];
$today_minutes = $today['minutes'];
$today_seconds = $today['seconds'];
$today_date = $today_year.$today_month.$today_day.$today_hours.$today_minutes.$today_seconds;
// The following echo returns: 20080118122430
echo $today_date . "<br />";

... some code ...												

// I get the deadline value from the database
$latest_prediction_deadline = $row['prediction_end'];
$temp = str_split($latest_prediction_deadline);
$clean = array();
foreach($temp as $value){
	if(is_numeric($value)){
		$clean [] = $value;
	}
}
$latest_prediction_deadline= implode($clean);
// The following echo returns: 20080118040100
echo $latest_prediction_deadline . "<br />";

// Here is a comparison I did, trying to subtract one date from another.  It echoed "0"
$some = $today_date - $latest_prediction_deadline;
echo $some . "<br />";

... code ...

// Here is the comparison:
if ($today_date < $latest_prediction_deadline) {
     more code
}
?>

[alefort]

Sigh, I just don't understand why you think the time stamp will make a lick of difference.  Sure, I can format it however I want, but I can do the same thing with getDate(), albeit with a bit more work.

 

I'll go ahead and make the modification, but it's only going to effect the 'Today's Date' variable.  I can't add a timestamp for Prediction deadline, since it's a date in the future.  I still strongly believe the problem is related to me appending a string to the date, then storing it as datetime in MySQL, not with the formating associated with getDate(), which works fine.

 

How do I fix the timestamp so that it shows minutes not months like it currently does.  In the example you gave, you used months (m) twice.

 

you can in fact make timestamps for future dates, check out

 

mktime()

[kjtocool]

Done.

 

I echoed out this:

 

 

It returned:

echo "Today: " . $today_date . "<br />";
echo "Deadline: " . $latest_prediction_deadline . "<br />";
$some = $today_date - $latest_prediction_deadline;
echo "Today - Deadline =: " . $some . "<br />";

 

Today: 2008-01-18 13:24:15

Deadline: 20080118040100

Today - Deadline =: -20080118038100

 

Should note, $today_date is still gotten by getDate(), not timestamp().  If you want me to try it with timestamp, I can, but I need to know what letter equals minutes, since in the example you gave me, you used (m), which is Months.

 

The answer seems to be 3hours 81minutes 0seconds ... I have no idea how it came up with that, I expected it to be 20080118094315

[p2grace]

Change $today_date to this:

 

$today_date = date("YmdHis");

[kjtocool]

It now prints out:

 

Today: 20080118133543

Deadline: 20080118040100

Today - Deadline =: 93443

 

This seems to work, since it just creates two giant numbers.  Then I can do a subtraction ... Deadline - Today, and then create a variable ... if that variable > 0 then the deadline hasn't passed, and they should be able to predict.  If it's < 0, then it's the opposite.

 

I think this will work.  Thanks for your help, I'm going to go do some tests!

[p2grace]

Good deal, if it works could you hit the "Topic Solved" button

 

Thank you!