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?

  • 1
    What type is time?
    – Mihai
    Aug 29 '14 at 7:38
  • i kept it varchar , i thought i can change its time using php function strtotime() Aug 29 '14 at 7:39
  • The answer to your question is no. You'll have to use PHP to get the data into formats which are suitable for MySQL and send valid MySQL SQL statements to the serveer. See here for MySQL's date and time functions - there are a fair few to choose from. Aug 29 '14 at 7:48
  • 1
    A good idea is to store dates as dates, times as times, numbers as numbers. If you keep this in mind when designing your database, you won't have this type of headaches later on. Aug 29 '14 at 15:33
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



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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.