MySQL:如何计算两个时间戳之间的营业时间和忽略周末 [英] Mysql : how to calculate business hrs between two timestamps and neglect weekends

查看：353
本文介绍了MySQL:如何计算两个时间戳之间的营业时间和忽略周末的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的表有示例数据，我需要计算两列中两个时间戳之间的营业时间.营业时间:上午9:00至下午5:00，而忽略周六和周日，我不考虑公共假期.有人可以提供一些有关如何实现这一目标的指导吗?我想要第3列中所述的所需输出，日期格式为:yyyy-mm-dd 

    Created date             Updated date         Business hrs
    2012-03-05 9:00am   2012-03-05 3:00pm             6
    2012-03-05 10:00am  2012-03-06 10:00am            9
    2012-03-09 4:00pm   2012-03-19 10:00am            2

解决方案

该问题表明不应考虑公共假期，因此此答案的确如此-计算工作时间时考虑了周末，但忽略了可能的公共假期. /p>

如果您需要考虑公众假期，则需要有一个单独的表，其中列出了公众假期的日期，该日期可能会因年份，州与州或国家/地区而异.主要公式可能保持不变，但是对于给定日期范围内的公共假期，您需要从其结果小时数中减去.

让我们创建一个包含一些示例数据的表格，其中涉及各种情况:

CREATE TABLE T (CreatedDate datetime, UpdatedDate datetime);

INSERT INTO T VALUES
('2012-03-05 09:00:00', '2012-03-05 15:00:00'), -- simple part of the same day
('2012-03-05 10:00:00', '2012-03-06 10:00:00'), -- full day across the midnight
('2012-03-05 11:00:00', '2012-03-06 10:00:00'), -- less than a day across the midnight
('2012-03-05 10:00:00', '2012-03-06 15:00:00'), -- more than a day across the midnight
('2012-03-09 16:00:00', '2012-03-12 10:00:00'), -- over the weekend, less than 7 days
('2012-03-06 16:00:00', '2012-03-15 10:00:00'), -- over the weekend, more than 7 days
('2012-03-09 16:00:00', '2012-03-19 10:00:00'); -- over two weekends

在MS SQL Server中，我使用以下公式:

SELECT
    CreatedDate,
    UpdatedDate,
    DATEDIFF(minute, CreatedDate, UpdatedDate)/60.0 -
    DATEDIFF(day,    CreatedDate, UpdatedDate)*16 -
    DATEDIFF(week,   CreatedDate, UpdatedDate)*16 AS BusinessHours
FROM T

哪个会产生以下结果:

+-------------------------+-------------------------+---------------+
|       CreatedDate       |       UpdatedDate       | BusinessHours |
+-------------------------+-------------------------+---------------+
| 2012-03-05 09:00:00     | 2012-03-05 15:00:00     | 6             |
| 2012-03-05 10:00:00     | 2012-03-06 10:00:00     | 8             |
| 2012-03-05 11:00:00     | 2012-03-06 10:00:00     | 7             |
| 2012-03-05 10:00:00     | 2012-03-06 15:00:00     | 13            |
| 2012-03-09 16:00:00     | 2012-03-12 10:00:00     | 2             |
| 2012-03-06 16:00:00     | 2012-03-15 10:00:00     | 50            |
| 2012-03-09 16:00:00     | 2012-03-19 10:00:00     | 42            |
+-------------------------+-------------------------+---------------+

它有效，因为在SQL Server DATEDIFF 返回指定的 datepart 边界在指定的开始日期和结束日期之间.

每天有8个工作小时.我计算两个日期之间的总小时数，然后减去午夜数乘以每天16个非工作时间，然后减去周末数乘以16(Sat + Sun为8 + 8个工作小时).

它还假定给定的开始和结束日期/时间在营业时间内.

在MySQL中，最接近的等效项是 TIMESTAMPDIFF ，但工作方式有所不同.它可以有效地计算以秒为单位的差异，并用所选的 unit 中的秒数除(舍弃小数部分).

因此，要获得所需的结果，我们可以计算某个锚点日期时间与CreatedDate和UpdatedDate之间的TIMESTAMPDIFF，而不是直接计算CreatedDate和UpdatedDate之间的差.

我选择了2000-01-03 00:00:00，这是一个星期一.您可以选择其他任何星期一(如果星期从星期日开始，也可以是星期日)午夜作为定位日期.

MySQL查询变为(请参阅 SQL Fiddle ):

SELECT
    CreatedDate,
    UpdatedDate,

    TIMESTAMPDIFF(MINUTE, CreatedDate, UpdatedDate)/60.0 -

    16*(
        TIMESTAMPDIFF(DAY,    '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(DAY,    '2000-01-03',CreatedDate)
    ) -

    16*(
        TIMESTAMPDIFF(WEEK,   '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(WEEK,   '2000-01-03',CreatedDate)
    ) AS BusinessHours

FROM T

My table has sample data and I need to calculate business hrs between two timestamps in two columns. Business hrs : 9:00am to 5:00pm and neglect Saturday and Sunday, I am not considering public holidays. Can someone please provide some guidelines on how to achieve this? I want desired output as stated in column 3 , date is in format of : yyyy-mm-dd

    Created date             Updated date         Business hrs
    2012-03-05 9:00am   2012-03-05 3:00pm             6
    2012-03-05 10:00am  2012-03-06 10:00am            9
    2012-03-09 4:00pm   2012-03-19 10:00am            2

解决方案

The question says that public holidays should not be considered, so this answer does just that - calculates business hours taking weekends into account, but ignoring possible public holidays.

If you need to take public holidays into account you'd need to have a separate table which would list dates for public holidays, which may differ from year to year and from state to state or country to country. The main formula may stay the same, but you'd need to subtract from its result hours for public holidays that fall within the given range of dates.

Let's create a table with some sample data that covers various cases:

CREATE TABLE T (CreatedDate datetime, UpdatedDate datetime);

INSERT INTO T VALUES
('2012-03-05 09:00:00', '2012-03-05 15:00:00'), -- simple part of the same day
('2012-03-05 10:00:00', '2012-03-06 10:00:00'), -- full day across the midnight
('2012-03-05 11:00:00', '2012-03-06 10:00:00'), -- less than a day across the midnight
('2012-03-05 10:00:00', '2012-03-06 15:00:00'), -- more than a day across the midnight
('2012-03-09 16:00:00', '2012-03-12 10:00:00'), -- over the weekend, less than 7 days
('2012-03-06 16:00:00', '2012-03-15 10:00:00'), -- over the weekend, more than 7 days
('2012-03-09 16:00:00', '2012-03-19 10:00:00'); -- over two weekends

In MS SQL Server I use the following formula:

SELECT
    CreatedDate,
    UpdatedDate,
    DATEDIFF(minute, CreatedDate, UpdatedDate)/60.0 -
    DATEDIFF(day,    CreatedDate, UpdatedDate)*16 -
    DATEDIFF(week,   CreatedDate, UpdatedDate)*16 AS BusinessHours
FROM T

Which produces the following result:

+-------------------------+-------------------------+---------------+
|       CreatedDate       |       UpdatedDate       | BusinessHours |
+-------------------------+-------------------------+---------------+
| 2012-03-05 09:00:00     | 2012-03-05 15:00:00     | 6             |
| 2012-03-05 10:00:00     | 2012-03-06 10:00:00     | 8             |
| 2012-03-05 11:00:00     | 2012-03-06 10:00:00     | 7             |
| 2012-03-05 10:00:00     | 2012-03-06 15:00:00     | 13            |
| 2012-03-09 16:00:00     | 2012-03-12 10:00:00     | 2             |
| 2012-03-06 16:00:00     | 2012-03-15 10:00:00     | 50            |
| 2012-03-09 16:00:00     | 2012-03-19 10:00:00     | 42            |
+-------------------------+-------------------------+---------------+

It works, because in SQL Server DATEDIFF returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

Each day has 8 business hours. I calculate total number of hours between two dates, then subtract the number of midnights multiplied by 16 non-business hours per day, then subtract the number of weekends multiplied by 16 (8+8 business hours for Sat+Sun).

It also assumes that the given start and end date/times are during the business hours.

In MySQL the closest equivalent is TIMESTAMPDIFF, but it works differently. It effectively calculates the difference in seconds and divides (discarding the fractional part) by the number of seconds in the chosen unit.

So, to get results that we need we can calculate the TIMESTAMPDIFF between some anchor datetime and CreatedDate and UpdatedDate instead of calculating the difference between CreatedDate and UpdatedDate directly.

I've chosen 2000-01-03 00:00:00, which is a Monday. You can choose any other Monday (or Sunday, if your week starts on Sunday) midnight for the anchor date.

The MySQL query becomes (see SQL Fiddle):

SELECT
    CreatedDate,
    UpdatedDate,

    TIMESTAMPDIFF(MINUTE, CreatedDate, UpdatedDate)/60.0 -

    16*(
        TIMESTAMPDIFF(DAY,    '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(DAY,    '2000-01-03',CreatedDate)
    ) -

    16*(
        TIMESTAMPDIFF(WEEK,   '2000-01-03',UpdatedDate)-
        TIMESTAMPDIFF(WEEK,   '2000-01-03',CreatedDate)
    ) AS BusinessHours

FROM T

这篇关于MySQL:如何计算两个时间戳之间的营业时间和忽略周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆