Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
What type is time? –  Mihai Aug 29 at 7:38
    
i kept it varchar , i thought i can change its time using php function strtotime() –  moussa houssein Aug 29 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. –  Vérace Aug 29 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. –  Daniel Hutmacher Aug 29 at 15:33

3 Answers 3

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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