Mysql

MySQL - 合併或拆分日期時間間隔（開始日期到結束日期）

  • November 24, 2021

我有一個表，其中儲存了一個活動列表，其中的時間間隔由 2 個日期分隔。

樣本：

+------+---------------------+---------------------+-------------+
| name |        start        |         end         | time (calc) |
+------+---------------------+---------------------+-------------+
|  me  | 2017-04-03 11:00:00 | 2017-04-03 11:30:00 |          30 |
|  me  | 2017-04-03 23:45:00 | 2017-04-04 00:15:00 |          30 |
|  me  | 2017-04-04 10:00:00 | 2017-04-04 11:00:00 |          60 |
|  me  | 2017-04-04 10:30:00 | 2017-04-04 11:30:00 |          60 |
|  me  | 2017-04-05 23:00:00 | 2017-04-05 23:30:00 |          30 |
|  me  | 2017-04-05 23:15:00 | 2017-04-07 00:45:00 |        1530 |
+------+---------------------+---------------------+-------------+

我想知道每個使用者每天（然後每週）佔用了多少分鐘，所以我需要將目前表轉換為共享部分空間時間的間隔合併為一個，而間隔合併為幾個天是分開的，就像下一個：

+------+---------------------+---------------------+-------------+
| name |        start        |         end         | time (calc) |
+------+---------------------+---------------------+-------------+
|  me  | 2017-04-03 11:00:00 | 2017-04-03 11:30:00 |          30 |
|  me  | 2017-04-03 23:45:00 | 2017-04-03 23:59:59 |          15 |
|  me  | 2017-04-04 00:00:00 | 2017-04-04 00:15:00 |          15 |
|  me  | 2017-04-04 10:00:00 | 2017-04-04 11:30:00 |          90 |
|  me  | 2017-04-05 23:00:00 | 2017-04-05 23:59:59 |          60 |
|  me  | 2017-04-06 00:00:00 | 2017-04-06 23:59:59 |        1440 |
|  me  | 2017-04-07 00:00:00 | 2017-04-07 00:45:00 |          45 |
+------+---------------------+---------------------+-------------+

然後輕鬆查詢它以獲得每天的分鐘數：

+------+------------+------+
| name |    day     | time |
+------+------------+------+
|  me  | 2017-04-03 |   45 |
|  me  | 2017-04-04 |  105 |
|  me  | 2017-04-05 |   60 |
|  me  | 2017-04-06 | 1440 |
|  me  | 2017-04-07 |   45 |
+------+------------+------+

我正在尋找資訊，我發現如何在這里合並多個日期間隔（mysql - sum interval dates），但是我無法在幾天內分割一個間隔。

可以在單個查詢中完成嗎？我怎麼能做到？

編輯：

SQL（結構和數據）：

CREATE TABLE activities (
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   name VARCHAR(45),
   start DATETIME,
   end DATETIME,
   time INT GENERATED ALWAYS AS (TIMESTAMPDIFF(MINUTE, start, end)) VIRTUAL
);

INSERT INTO activities (name, start, end) VALUES
('me','2017-04-03 11:00','2017-04-03 11:30'),
('me','2017-04-03 23:45','2017-04-04 00:15'),
('me','2017-04-04 10:00','2017-04-04 11:00'),
('me','2017-04-04 10:30','2017-04-04 11:30'),
('me','2017-04-05 23:00','2017-04-05 23:30'),
('me','2017-04-05 23:15','2017-04-07 00:45');

SQL合併多個間隔（來源：mysql - sum interval dates）：

SELECT name, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time
FROM (
   SELECT x.name, x.start, min(y.end) AS end 
   FROM activities AS x 
   JOIN activities AS y 
       ON x.name = y.name 
      AND x.start <= y.end 
      AND NOT EXISTS (
          SELECT 1 
          FROM activities AS z 
          WHERE y.name = z.name 
            AND y.end >= z.start 
            AND y.end < z.end
      ) 
   WHERE NOT EXISTS (
       SELECT 1 
       FROM activities AS u 
       WHERE x.name = u.name 
         AND x.start > u.start 
         AND x.start <= u.start
   ) 
   GROUP BY x.name, x.start
) AS v GROUP BY name, end;

首先，由於您需要生成一系列日期，我建議使用calendar表格。

CREATE TABLE if not exists calendar (
   mdate date PRIMARY KEY NOT NULL
);

INSERT INTO calendar values
('20170403'),('20170404'),('20170405'),('20170406'),('20170407'),('20170408');

他們是怎麼做到的呢

只是為了獲得重疊的活動，我使用了您在問題中提供的查詢。

create view overlaped_activities
as
SELECT name, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time
FROM (
   SELECT x.name, x.start, min(y.end) AS end 
   FROM activities AS x 
   JOIN activities AS y 
       ON x.name = y.name 
      AND x.start <= y.end 
      AND NOT EXISTS (
          SELECT 1 
          FROM activities AS z 
          WHERE y.name = z.name 
            AND y.end >= z.start 
            AND y.end < z.end
      ) 
   WHERE NOT EXISTS (
       SELECT 1 
       FROM activities AS u 
       WHERE x.name = u.name 
         AND x.start > u.start 
         AND x.start <= u.start
   ) 
   GROUP BY x.name, x.start
) AS v GROUP BY name, end;

首先，我計算從開始日期到午夜的分鐘數：

if(date(start) = date(end), 
 time_to_sec(timediff(end, start)) / 60, 
 (1440 - time_to_sec(time(start)) / 60)) mstart

然後，如果 start <> end，我計算從午夜到結束日期的分鐘數：

if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) mend

這會返回一個像這樣的表：

| start               | end                 |     mdiff |  mstart |    mend |
|---------------------|---------------------|----------:|--------:|--------:|
| 03.04.2017 11:00:00 | 03.04.2017 11:30:00 |   30,0000 | 30,0000 |       0 |
| 03.04.2017 23:45:00 | 04.04.2017 00:15:00 |   30,0000 | 15,0000 | 15,0000 |
| 04.04.2017 10:00:00 | 04.04.2017 11:30:00 |   90,0000 | 90,0000 |       0 |
| 05.04.2017 23:00:00 | 07.04.2017 00:45:00 | 1545,0000 | 60,0000 | 45,0000 |

這很好，但這裡還有另一個問題：

| 05.04.2017 23:00:00 | 07.04.2017 00:45:00 | 1545,0000 | 60,0000 | 45,0000 |

當然：1545 &lt;&gt; 60 + 45

我們需要在開始日期和結束日期之間生成一系列日期，並為每一天添加 1440 分鐘。

我們可以使用日曆表來獲取它：

  select   name,
           mdate date_activity,
           sum(1440) minutes
  from     calendar
  join     overlaped_activities
  on       calendar.mdate &gt; date(start)
  and      calendar.mdate &lt; date(end)
  where    datediff(end, start) &gt; 1
  group by name, mdate

好的，我們得到了所有的原料，是時候做菜譜了：

select name, date_activity, sum(minutes) min_activity
from (
      select name, 
             date(start) date_activity,
             if(date(start) = date(end), time_to_sec(timediff(end, start)) / 60, (1440 - time_to_sec(time(start)) / 60)) minutes
      from overlaped_activities

      UNION ALL

      select name, 
             date(end) date_activity,
             if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) minutes
      from overlaped_activities

      UNION ALL

      select   name,
               mdate date_activity,
               sum(1440) minutes
      from     calendar
      join     overlaped_activities
      on       calendar.mdate &gt; date(start)
      and      calendar.mdate &lt; date(end)
      where    datediff(end, start) &gt; 1
      group by name, mdate
   ) act
group by name, date_activity;

最後結果：

| name |       date_activity | min_activity |
|------|--------------------:|-------------:|
| me   | 03.04.2017 00:00:00 |      45,0000 |
| me   | 04.04.2017 00:00:00 |     105,0000 |
| me   | 05.04.2017 00:00:00 |      60,0000 |
| me   | 06.04.2017 00:00:00 |    1440,0000 |
| me   | 07.04.2017 00:00:00 |      45,0000 |

差點忘了，菜譜：http ://rextester.com/EIJOI20983

引用自：https://dba.stackexchange.com/questions/169186

相關問答

Mysql

基於 NOW() 選擇結果

  • June 20, 2018
檢視問答
Mysql

轉換日期格式以插入 MySQL 數據庫

  • March 23, 2022
檢視問答
Mysql

在 MySQL 中查找時間線之間的發生次數和時間間隔

  • January 31, 2022
檢視問答
Sql-Server

計算區間表的集差

  • January 9, 2022
檢視問答
Mysql

從星期四或一周中的任何其他日子開始，如何通過每週結果改進我的 SQL 語句？

  • December 31, 2021
檢視問答
Mysql

MySQL：有沒有辦法根據日期動態生成多個日期範圍

  • November 28, 2021
檢視問答