What is the equivalent of this function of php in mysql:
$cba=date_diff($cb,$ca); $cba->format('%R%a.%h');
output: Date: 1.12 //that's 1day and 12 hours
Here's where I'm going to use it for:
DATEDIFF(q2.adate, q1.deact) AS ND,
Can i do the same process in mysql alone, Thanks
Try this using TIMEDIFF
SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF(q2.adate, q1.deact)) / 24), ' days, ',
MOD(HOUR(TIMEDIFF(q2.adate, q1.deact)), 24), ' hours, '
)
AS ND
MySQL TIMEDIFF() returns the differences between two time or datetime expressions. It is to be noted that two expressions must be of same type.
From MySQL Docs:
DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
You can use TIMEDIFF instead:
TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
So the result of TIMEDIFF is like:
-00:00:00.000000 // Negative times 00:00:00.000000 // Positive times
or TIMESTAMPDIFF:
TIMESTAMPDIFF() returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.
So:
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
will return 128885 (minutes).