The difference between the two lines with the time and day of the week

Question

I saved to the database twice

DayTime1 = "Wed 09:00" DayTime2 = "Wed 13:00" 

I want to get the difference between these two dates in minutes.

 TIMESTAMPDIFF(MINUTES,DayTime1,DayTime2) return null 

I can’t get through this. Is there any way to get the difference?

Answer 1

Note that SELECT STR_TO_DATE("Wed 09:00", "%a %H:%i") returns 0000-00-00 09:00:00 (at least on my local MySql 5.5.16). Therefore, if you compare different days, you will not get the correct result.

If the year and week are specified, the day name will be interpreted as of the current date, so comparisons may also cover days. For example (although not very elegant, I admit):

 SELECT TIMESTAMPDIFF(MINUTE, STR_TO_DATE(CONCAT(YEAR(CURDATE()), WEEKOFYEAR(CURDATE()), ' Tue 09:00'), '%x%v %a %H:%i'), STR_TO_DATE(CONCAT(YEAR(CURDATE()), WEEKOFYEAR(CURDATE()), ' Wed 13:00'), '%x%v %a %H:%i') ) 

Answer 2

Since you also included php -tag, I assume the PHP solution is valid as well:

 $daytime1 = "Wed 09:00"; $daytime2 = "Wed 13:00"; $diff = abs(strtotime($daytime1) - strtotime($daytime2)); //absolute diff in seconds $diff = $diff / 60; //Difference in minutes 

EDIT:

And here is a clean MySQL solution:

 SELECT ABS( TIME_TO_SEC( TIMEDIFF( STR_TO_DATE("Wed 09:00", "%a %H:%i"), STR_TO_DATE("Wed 13:00", "%a %H:%i") ) ) ) / 60 

The second parameter "%a %H:%i" is the date format. If it is always in the format "The first three letters of the day of the week, space, hour with an initial zero, : , minutes", you can use this format.