[RESOLVED] Select query output minus 1 day

dcjones

Hi all,

I am trying to write a query that outputs records that have a date minus 1 day.

This is what I am working with but I am not getting the correct results.

I know its because the query is wrong but I have written it all ways apart from the right way. Can anyone point me in the right direction.

SELECT DISTINCT CarrierCode, FlightNumber, FlightDate, ScheduledDateTime FROM flightdata WHERE FlightDate BETWEEN NOW() - INTERVAL 1 DAY AND NOW()

Crowly

Take a look at this post: http://phpbuilder.com/board/showthread.php?t=10373988

dcjones

Hi Crowly,

Thanks for the reply and thats just what I needed.

Problem is it gives an error

The code I am using is:

DELETE  FROM flightdata WHERE FlightDate BETWEEN NOW() AND NOW() - INTERVAL 1 DAY

FlightDate is a type DateTime feild, Would the above be be correct or am I missing something?

bradgrafelman

The BETWEEN clause should be "BETWEEN min AND max".

Crowly

NOW() returns both a time and a date, not quite sure how mysql handles it if the date field is just DATE and not DATETIME. You might need to format what NOW() returns to not include the time, just the date.

bradgrafelman

Crowly wrote:
not quite sure how mysql handles it if the date field is just DATE and not DATETIME

Seamlessly, as a matter of fact. However...

Crowly wrote:
You might need to format what NOW() returns to not include the time, just the date.

...which could be accomplished by switching "NOW()" to "CURDATE()".

dcjones

Hi all,

Can anyone see why this is producing an error. I've been playing around with on and off all day.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

The code below starts at line 379.

mysql_select_db($database_flightq, $flightq);
	$query_deleteflights = mysql_query("DELETE  FROM flightdata WHERE FlightDate BETWEEN CURDATE() AND CURDATE() - INTERVAL 1 DAY ");
	$flights = mysql_query($query_deleteflights, $flightq) or die(mysql_error());
	$row_flights = mysql_fetch_assoc($flights);
	$totalRows_flights = mysql_num_rows($flights);
	print "Flight records deleted: " .$totalRows_flights;

bradgrafelman

Other than the fact that your WHERE condition will never match any rows, no I don't see anything wrong with the query such that you would get a syntax error.

What version of MySQL are you using?

dcjones

Hi Brad,

MySQL Ver 5.0.22

Again, many thanks for your time.

dcjones

Hi Brad,

I have changed the way I am processing the data.

I created a new feild in the data table named "deleteTime", When records are updated if they have a status code of "2" or "5" the current datetime plus 1 hour is written to that feild.

if($Stat == 2 || $Stat == 5){
	$updateSQL = sprintf("UPDATE flightdata SET DeleteTime=%s WHERE AODBUniqueID=%s",

                   GetSQLValueString($_POST['DeleteTime'] = $deleteTime = date("Y-m-d H:i:s", strtotime ("+1 hour")), "date"),
                   GetSQLValueString($_POST['AODBUniqueID'] = $AODBUniqueID, "text"));

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

At the end of the script I have the code below which deletes all records that match:

mysql_select_db($database_flightq, $flightq);
	$query_deleteflights = mysql_query("DELETE  FROM flightdata WHERE CURRENT_TIMESTAMP > DeleteTime");
	$flights = mysql_query($query_deleteflights, $flightq) or die(mysql_error());
	$row_flights = mysql_fetch_assoc($flights);
	$totalRows_flights = mysql_num_rows($flights);
	print "Flight records deleted: " .$totalRows_flights;

But I do get an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1.

for the life of me I can't see whats causing the error but the record deletion works.

Any ideas as to why the error is triggered.

Many thanks for any time you spend looking at this.

dcjones

Hi all,

I have suceeded in removing my error. I changed the following:

mysql_select_db($database_flightq, $flightq);
    $query_deleteflights = mysql_query("DELETE  FROM flightdata WHERE CURRENT_TIMESTAMP > DeleteTime");
    $flights = mysql_query($query_deleteflights, $flightq) or die(mysql_error());
    $row_flights = mysql_fetch_assoc($flights);
    $totalRows_flights = mysql_num_rows($flights);
    print "Flight records deleted: " .$totalRows_flights;

mysql_select_db($database_flightq, $flightq);
	$query_deleteflights = mysql_query("DELETE  FROM flightdata WHERE CURRENT_TIMESTAMP > DeleteTime");

Thanks for all your input.