Convert datetime inside a query

Can you c开发者_StackOverflowompare a DATETIME to a UNIX timestamp on the fly in a $query?

Something like:

$query = "DELETE FROM customer_appointments 
           WHERE dealer_id = 12880 
             AND $today < $appt_date";

$today = time()

$appt_date = "2011-09-17 00:00:00", is a field from customer_appointments.

Can't you compare these two right in the query?

Try:

DELETE FROM customer_appointments WHERE dealer_id = 12880 AND $today < UNIX_TIMESTAMP( '$appt_date' );

That should work for MySQL (unsure what DB You're using).

Also, I suggest you escape your data to prevent Bobby Tables from getting into your database.

There are a few ways what you're asking can be done. You can use date(Y-m-d H:i:s) instead of time, You can convert the appt_date to UNIX_TIMESTAMP inside of MySQL, or you can convert it in PHP using strtotime.

I think you're actually trying to do this though:

DELETE FROM customer_appointments 
       WHERE dealer_id = 12880 
         -- all appointments before now
         AND NOW() < APPT_DATE

Mysql can convert dates from and to unix timestamp with no problem.
However, see no use for the unix timestamp in your question

DELETE FROM customer_appointments WHERE dealer_id = 12880 AND NOW() < appt_date

looks like a solution.
If not - you have to describe your task in words.

You can use FROM_UNIXTIME($today) in the query which will allow you to compare the datetime with the timestamp

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime