MySQL：计算日期/时间之间的差异 - 仅在M-F“工作周”期间 [英] MySQL: Calculate the difference between Date/Times - only during M-F "Work week"

问题描述

我需要计算开始日期/时间与结束日期/时间之间的差异。但是，我只想在5天的工作周内（以星期日/日为周日排除）。这样做最好的方法是什么？我的想法是，从这一天起，我必须得到一周的日子，如果是一个工作日，那么我添加到累加器。如果没有，那么我不添加任何东西。

I need to calculate a difference between a starting date/time and an ending date/time. But, I only want to do this for the 5-day work week (exclude Sat/Sun as days). What is the best way to do this? My thought is that from the date, I'll have to get the day of the week and if it is a weekday, then I add to the accumulator. If it's not, then I don't add anything.

我相信有人以前做过这个，但似乎找不到任何搜索。任何链接或其他帮助将是非常有用的。

I'm sure someone has done this before, but I couldn't seem to find anything searching. Any links or other help would be very useful.

非常感谢，

Bruce

推荐答案

DAYOFWEEK星期日返回1，星期六返回7。我不知道你的架构是如何设置的，但是这将在周一至周五工作周内执行两个日期的TIMEDIFF。

DAYOFWEEK returns 1 for Sunday and 7 for Saturday. I'm not sure how your schema is set up, but this will perform a TIMEDIFF of two dates that are on a Monday - Friday work week.

select TIMEDIFF(date1,date2) from table 
where DAYOFWEEK(date1) not in (1,7) and DAYOFWEEK(date2) not in (1,7)

MySQL DATE / TIME功能

编辑：从布鲁斯关于假期的评论。如果你有一张充满假期的表，那么这样的工作可以排除处理那些日子：

EDIT: From Bruce's comment about holidays. If you have a table full of holiday dates, something like this would work to exclude processing those days:

select TIMEDIFF(date1,date2) from table 
where date1 not in (select holiday from holiday_table) and
date2 not in (select holiday from holiday_table) and
DAYOFWEEK(date1) not in (1,7) and DAYOFWEEK(date2) not in (1,7)

这篇关于MySQL：计算日期/时间之间的差异 - 仅在M-F“工作周”期间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！