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

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?

我无法通过它。有什么方法可以改变吗？

2 个解决方案

#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.

请注意，SELECT STR_TO_DATE（“Wed 09:00”，“％a％H：％i”）返回0000-00-0009：00：00（至少在我的本地MySql 5.5.16上）。因此，如果比较不同的日期，您将无法获得正确的结果。

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')
)

#2

Since you also included the php-tag, I'm assuming a PHP solution is valid as well:

既然你也包含了php-tag，我假设PHP解决方案也是有效的：

$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 pure MySQL solution:

这是一个纯粹的MySQL解决方案：

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's always in the format of "First three letters of weekday, space, hour with leading zero, :, minutes" then you can use this format.

第二个参数“％a％H：％i”是日期格式。如果它始终采用“工作日的前三个字母，空格，带前导零的小时，：，分钟”的格式，那么您可以使用此格式。

#1