Can I use PHP function strtotime() in SQL queries?

Question

I have this table, and I need to use a PHP function in the query.

id     time       lastTime
1      08:02
2      08:04
3      08:30

I am using this query with the strtotime(), and it's not working:

UPDATE table
SET lastTime = strtotime(time) - strtotime('08:00')";

How can it be done?

Answer 1

UPDATE table
SET lasttime=TIME_FORMAT(SEC_TO_TIME(((SUBSTRING_INDEX(time,':',1)*60)+SUBSTRING_INDEX(time,':',-1))-480),'%i:%s');

Do the calculation in seconds then back to time,no need for php

FIDDLE

Answer 2

Your $date have to be the correct MySQL date format : '2014-08-29 10:03:30'

You can use this:

$date = date('Y-m-d H:i:s', strtotime($time));
$sqlTemplate = "UPDATE table SET lastTime = %s" ;
$sql = sprintf($sqlTemplate, $date);

Checkout the formats that can be your $time string : http://php.net/manual/en/function.strtotime.php and http://php.net/manual/en/datetime.formats.php

Answer 3

If you are doing date-time math in mysql, use mysql's built-in functions.

However, if most of your processing is in PHP, then it is much more efficient to store time in the database as INT - do the math as needed, format it once at output time. Many advantages including consistent timezone functions.

I generally pre-process my queries in PHP, so the database gets something like

select * from table where mytime between 1389404807 and 1409404807

instead of the more convoluted date_sub() functions. Converting back and forth between human-readable dates and the underlying integer formats just to knock off an hour is just silly.