php - Formatting MySQL IF statement with date_format
Get the solution ↓↓↓I have a MySQL query, which is supposed to calculate the difference in time between the fields, but due to the query, i am unable to format it as a time through the interface, so its not calculating correctly, so I was trying to add the DATE_Format to the query, but no matter what I tried, nothing works.
Here is the query I am using:IF(`Time Elapsed` < `Speed Fault`, `Speed Fault` - `Time Elapsed`, 0) AS `Under Time` ,
I tried to add:DATE_FORMAT(`Under Time`, `%i:%s`)
to the mix some how so the field will display the minutes and seconds.
Currently with the above query, its returning the correct amount of seconds, if the difference in under 60 seconds, but if its for example 3 minutes and 5 sec difference, the query returns a value of 305, as so on...
So i am hoping someone can help me out. I am open to doing this in either PHP or MySQL.
Answer
Solution:
Doing datestamp arithmetic is tricky. In MySQL, using DATETIME or TIMESTAMP data in a numerical context (for example by subtracting one from another) can get strange results.
I guess you want your results in a format like1:23:45
, hours / minutes / seconds. In that case this is a job for TIMEDIFF(later, earlier).
IF(`Time Elapsed` < `Speed Fault`,
TIMEDIFF(`Speed Fault` - `Time Elapsed`),
SEC_TO_TIME(0)) AS `Under Time`
If you want your results in seconds, use TIMESTAMPDIFF(unit, earlier, later) and try this
GREATEST(TIMESTAMPDIFF(SECOND,`Time Elapsed`, `Speed Fault`),0) AS `Under Time`
You probably noticed the eariler / later parameters to the two functions are in opposite order. Database time arithmetic functions are like that.
And, be careful with formatted TIME data. It cannot show a time interval greater than or equal to 839 hours. If you try, it shows838:59:59
without warning you.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: cannot set properties of undefined (setting '_dt_cellindex')
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.