两个字符串与时间和工作日之间的差异

Question

I have two times saved in database as

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'm not able to get through it. Is there any way to get difference?

Answer 1

Please 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). So if comparing different days, you won't get the correct result.

If given a year and week, the day name will be interpreted to a real date, so comparisons may also span days. For example (though not really 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')
)