MySQL time query

dcjones

Hi all,

I have a table which holds a fields type "DATE", I am trying to write a query that would output all the records that have a current time which is less than the date/time held in the table.

The code I am using is:
[PHP
$nTime = date("Y-m-d H:i:s");

$query_nonactive = "SELECT * FROM flightdata WHERE RollOffTime < " . $nTime ;

[/code]

Any help would be great.

Krik

$nTime = date("Y-m-d H:i:s");

is creating a date like so 2010-08-26 13:01:34.

But the "DATE" field type only holds the date (2010-08-26). If you're trying to find out if "$nTime" is greater than (<) that field it may give you trouble. Either make the field a "DATETIME" type or change "$nTime" to only have the value of the date.

Now as your question does emit a bit of confusion I am wondering if "RollOffTime" is not just the time (HH:MM:SS) in which case you need "$nTime" to hold only a time value.

dcjones

Hi Krik,

Many thanks for your reply.

I am sorry I make a mistake in my post, it should have been;

I have a table which holds a fields type "DATETIME", I am trying to write a query that would output all the records that have a current time which is less than the date/time held in the table.

If I test the output of $nTime it is in the format of "2010-08-26 13:01:34"and the
field in the datatable "RollOffTime" also has a DATETIME type.

The code I am using is:
[PHP
$nTime = date("Y-m-d H:i:s");

$query_nonactive = "SELECT * FROM flightdata WHERE RollOffTime < " . $nTime ;

[/code]

Krik

I am trying to write a query that would output all the records that have a current time which is less than the date/time held in the table

That don't make any sense. You want to output records where that records time is less than it's time. Not possible, as records time is its time and it can not be less than or greater than what it is. I am guessing you misstated your question.

Now your query say you want to find record where "RollOffTime" is less than (<)"$nTime". $nTime looks to be current time so if are you are try to get records that are prior to now (in the past) that will work. If you are trying to get records that are coming up (in the future) use the ">" instead.

If that isn't the answer you need maybe try restating your question. Or give us a sample of what your query is outputting and what you would like it to be outputting.

dcjones

Hi Krik,

I think your right, I need to ask the question again. Here goes.

I have a data table which has a field (type datetime) called "RollOffTime", the stored and output format looks like this: "2010-08-27 17:30:14".

I have a variable "$nTime" which holds the current date and time. The output format for this variable looks like this; "2010-08-27 20:45:17"

What I am tyring to do is run a sql query that will update all the records where the stored data "RollOffTime" is less than the current time.

Example: if the RollOffTime is less than the current time, update the record. The code I am using is as follows;

$nTime = date("Y-m-d H:i:s");

$query_nonactive = "UPDATE flightdata SET   FIDSFlightDisplay = 'N' WHERE RollOffTime < " . $nTime ;

bradgrafelman

Couple of comments:

Adding the time portion of the date (e.g. giving it a 'datetime' instead of a 'date' value) won't cause problems, per sé, though it is a bit superfluous if it's a DATE column you're comparing the value to.
Dates are not numeric values, thus they must be delimited with quotes in the SQL query string.
There's no need to generate the date in PHP and insert it into the query string - MySQL has plenty of date and time functions (such as NOW(), CURDATE(), etc.).

Krik

Here's what you need, and I added in brad's suggestions

$query_nonactive = "
    UPDATE `flightdata` 
    SET  `FIDSFlightDisplay` = 'N', `RollOffTime` =  NOW() 
    WHERE `RollOffTime` <  NOW() 
";

I would note that one second after that script is run the same records could be updated again. In fact if you have a large enough number of records to update by the time it is finished you could run it again. Hopefully you have some other factor controlling when that is run.

dcjones

Hi Krik,

I used the code but it changed all the "RollOffTime" dates to the NOW time.

My code is as follows:

After running a query on the table I look for any records that have the "FIDSFlightActive" field set to "N", if the record is set to N then process the "if" statement to UPDATE any records where the "RollOffTime" is less than the current time.

if($row_flight['FIDSFlightActive'] == 'N' ) {

$updateSQL = sprintf("UPDATE `flightdata` SET  `FIDSFlightDisplay` = 'N'  WHERE `RollOffTime` <  NOW() ");

mysql_select_db($database_flightq, $flightq);
$Result1 = mysql_query($updateSQL, $flightq) or die(mysql_error());

}

Krik, many thanks for your time with this.

Krik

I think I misunderstood your latter question to mean you wanted the time updated your just wanting to update the "FIDSFlightDisplay" if the time is less than the current time. Your current query should work but with one small tweak.

The "sprintf" in your query string, it looks to have no purpose so it probably doesn't need to be there.

$updateSQL = "UPDATE `flightdata` SET  `FIDSFlightDisplay` = 'N'  WHERE `RollOffTime` <  NOW() ";