Mysql – use PHP function strtotime() in SQL queries

functionsMySQLtime

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?

Best Answer

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

Related Question