Working Out A Date Has Passed

[Supa Comix 0]

Right i am curious to how i would work out that exactly 24 hours has passed from a given variable. The variable is taken from a mysql database and reads as follows2007-11-22 10:05:49How would i check if 24 hours has passed or not? So it will work out that the 24 hours has passed at:2007-11-23 10:05:49I have tried datediff from mysql but that didn't quite work. Anyhelp would be great thanks.

Edited November 22, 2007 by Supa Comix (see edit history)

[vujsa 0]

Right i am curious to how i would work out that exactly 24 hours has passed from a given variable. The variable is taken from a mysql database and reads as follows
2007-11-22 10:05:49

How would i check if 24 hours has passed or not? So it will work out that the 24 hours has passed at:

2007-11-23 10:05:49

I have tried datediff from mysql but that didn't quite work. Anyhelp would be great thanks.

Well, generally speaking, I always recommend using UNIX timestamps instead of the formated date string MySQL uses. The UNIX timestamp is a count of the number of second since 1970-01-01 00:00:00. Since this is a number, it is easy to compare, manipulate, and format. MySQL even has a function that will convert UNIX timestamps to the typical MySQL timestamp: FROM_UNIXTIME

Assuming that you have built your system to use the YYYY-MM-DD HH:MM:SS format or you are using a system that someone else designed and you won't be changing the timestamp format, you'll need to work a little harder here.

You'll need to get the results from your database in UNIX time with the MySQL UNIX_TIMESTAMP() function.
Like so:

SELECT created
FROM my_table WHERE (
UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP(created)
) > ( 24 *60 *60 )

This would only select entries from your database that were older than 24 hours (86400 seconds). You could of course do much of this work with PHP and get the same result but you don't need to so why bother...

Using much more basic queries with PHP as the primary method of calculating the difference between now and when the entry was added would likely result in 2 or more queries and more queries means more work for the server and a slower site...

By the way, thanks for this topic. It forced me to do some MySQL time function research that I have been putting off too long.

Hope this helps.

vujsa

[Supa Comix 0]

so if i used the code:

$sql = mysql_query("SELECT turnsusedup FROM tbl_ship WHERE shipid='$shipid' AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(turnsusedup) > (24 *60 *60))") or die("Line 52: " . mysql_error());	if (isset($sql)){		//do something	}

then this would retrieve the date if it was greater then 24 hours and then '//do something'?

[vujsa 0]

That sounds about right but I don't have the time right now to set up a test database and script to test it for you...

My suggestion is to run a few tests...

$sql = mysql_query("SELECT turnsusedup FROM tbl_ship WHERE shipid='$shipid' AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(turnsusedup) > (24 *60 *60))") or die("Line 52: " . mysql_error());	if (isset($sql)){		echo "Record found!";	}

But remember, if you do not specify a specific record to look at, then many records may be returned. As a result, the first record may not be the only record and may not be the one you want.

Also, you can have the offset time set as a PHP variable:

$offset_time = 24 * 60 * 60;$sql = mysql_query("SELECT turnsusedup FROM tbl_ship WHERE shipid='$shipid' AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(turnsusedup) > $offset_time)") or	 die("Line 52: " . mysql_error());	if (isset($sql)){		echo "Record found!";	}

By using a couple of variables in your query, you can actually perform a few tests easier. For example, if you change the time to a small value, then you can probably return more results at once. I suggest echoing each result returned and echoing the original query:

$offset_time = 24 * 60 * 60;$query = "SELECT turnsusedup FROM tbl_ship WHERE shipid='$shipid' AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(turnsusedup) > $offset_time)";$sql = mysql_query($query) or die("Line 52: " . mysql_error());	if (isset($sql)){		echo $query . "<br>\n";		while ($row = mysql_fetch_array($sql)) {			echo $row[0] . "<br>\n";		}	}

This will show you exactly what your query looks like to the server and what results if any it is returning. This type of testing is very helpful for not only finding problems with your code but also getting more familiar with how the process works. I found that by seeing seeing how a complex query filled with several PHP variable looks to the server, I can write better queries. My SQL skills really improved after I started echoing additional information from the script like this.

Hope this helps,
vujsa

[Supa Comix 0]

Thanks but its not particulary working as i planned it.I have done some testing and playing around and as far as i know its not picking up if exactly 24 hours has passed and thus is printing the code (for example i am using the example which prints "echo $row[0] . "<br>";" I even tested it entering the data where it should come up with the correct information but even that didn't work. I have had a look at other bits of code but i am intrigued to this i even altered your code but none fitted or worked properly.

[vujsa 0]

What if anything is the script outputting right now.

try this to see if it is working.

$offset_time = 24 * 60 * 60;$query = "SELECT turnsusedup FROM tbl_ship WHERE shipid='$shipid' AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(turnsusedup) > $offset_time)";$sql = mysql_query($query) or die("Line 52: " . mysql_error());	if (isset($sql)){		echo $query . "<br>\n";		while ($row = mysql_fetch_array($sql)) {			echo $row[0] . "<br>\n";		}	}print_r($row);

Also, you might want to see what you are having returned without the IF statement:

$offset_time = 24 * 60 * 60;$query = "SELECT turnsusedup FROM tbl_ship WHERE shipid='$shipid' AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(turnsusedup) > $offset_time)";$sql = mysql_query($query) or die("Line 52: " . mysql_error());		echo $query . "<br>\n";		while ($row = mysql_fetch_array($sql)) {			echo $row[0] . "<br>\n";			$data[] = $row;		}echo "<pre>";print_r($data);echo "</pre>";

Additionally, the query I gave you wasn't written to check for EXACTLY 24 hours since it is unlikely that your script will ever run exactly 24 hours after the event. The query checks to see if 24 hours or more have passed.

I'm sorry, my database skills have gotten a little rusty since I started using database classes which has made me lazy.

vujsa

[Supa Comix 0]

Awesome its working properly now. Thank you very much