MySQL-在所有间隔之间插入行日期和时间

发布于 2025-01-11 18:58:33 字数 1427 浏览 0 评论 0原文

我有一个像这样的mysql数据库，

---------------------------------------------------------------------------
|   startdate   |   starttime  |    enddate    |    endtime   |   status  |
---------------------------------------------------------------------------
|  2020-03-04   |   04:30:00   |  2020-03-04   |   09:00:00   |  running  |
|  2020-03-04   |   11:30:00   |  2020-03-04   |   19:30:00   |  running  |
|  2020-03-05   |   05:00:00   |  2020-03-05   |   11:15:00   |  running  |
|  2020-03-05   |   12:30:00   |  2020-03-05   |   22:08:00   |  running  |

---------------------------------------------------------------------------

我想知道是否可能，创建一个php（或类似的东西）脚本来插入日期/时间之间的所有间隔并创建状态为“已停止”的行。

示例：

---------------------------------------------------------------------------
|   startdate   |   starttime  |    enddate    |    endtime   |   status  |
---------------------------------------------------------------------------
|  2020-03-04   |   00:00:00   |  2020-03-04   |   04:30:00   |  stopped  | *created by this script
|  2020-03-04   |   04:30:00   |  2020-03-04   |   09:00:00   |  running  |
|  2020-03-04   |   09:00:00   |  2020-03-04   |   11:30:00   |  stopped  | *
|  2020-03-04   |   11:30:00   |  2020-03-04   |   19:30:00   |  running  |

etc.

---------------------------------------------------------------------------

这可能吗？

对不起我的英语

原文

I have a mysql database like this

---------------------------------------------------------------------------
|   startdate   |   starttime  |    enddate    |    endtime   |   status  |
---------------------------------------------------------------------------
|  2020-03-04   |   04:30:00   |  2020-03-04   |   09:00:00   |  running  |
|  2020-03-04   |   11:30:00   |  2020-03-04   |   19:30:00   |  running  |
|  2020-03-05   |   05:00:00   |  2020-03-05   |   11:15:00   |  running  |
|  2020-03-05   |   12:30:00   |  2020-03-05   |   22:08:00   |  running  |

---------------------------------------------------------------------------

I want know if is possible, create a php(or something like that) script to insert all intervals between date/time and create a row with status "stopped".

Example:

---------------------------------------------------------------------------
|   startdate   |   starttime  |    enddate    |    endtime   |   status  |
---------------------------------------------------------------------------
|  2020-03-04   |   00:00:00   |  2020-03-04   |   04:30:00   |  stopped  | *created by this script
|  2020-03-04   |   04:30:00   |  2020-03-04   |   09:00:00   |  running  |
|  2020-03-04   |   09:00:00   |  2020-03-04   |   11:30:00   |  stopped  | *
|  2020-03-04   |   11:30:00   |  2020-03-04   |   19:30:00   |  running  |

etc.

---------------------------------------------------------------------------

Is this possible?

Sorry my english

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酷到爆炸 2025-01-18 18:58:33

假设您使用的是 MySQL 8+，您可以使用 LAG() 函数将当前记录的开始日期/时间与上一条记录的结束日期/时间进行比较。当存在差异时，使用这些值来创建缺失的时间跨度：

上一个结束日期/时间 ==>新开始日期/时间
当前开始日期/时间 ==>新结束日期/时间

查询：

此查询将返回缺失的记录，您可以根据需要将其插入表中。

WITH cte AS (
   -- using single datetime value for simpler logic
   SELECT * 
      , LAG (STR_TO_DATE(CONCAT(EndDate, ' ', EndTime), '%Y-%m-%d %H:%i:%s'), 1, NULL) 
            OVER (ORDER BY EndDate, EndTime) AS PrevEndDateTime
      , STR_TO_DATE(CONCAT(StartDate, ' ', StartTime), '%Y-%m-%d %H:%i:%s') AS StartDateTime            
   FROM   YourTable
)
SELECT CAST( DATE_FORMAT(COALESCE(PrevEndDateTime, StartDate),'%Y-%m-%d') AS DATE ) AS StartDate
       , CAST( DATE_FORMAT(COALESCE(PrevEndDateTime, StartDate),'%H:%i:%s') AS TIME ) AS StartTime
       , StartDate AS EndDate
       , StartTime AS EndTime
       , 'stopped' AS Status
FROM   cte
WHERE  StartDateTime <> PrevEndDateTime
OR     PrevEndDateTime IS NULL

测试数据：

开始日期 |开始时间 |结束日期 |结束时间 |地位 
:--------- | :-------- | :--------- | :----- | :------
2020-03-02 | 01:30:00 | 01:30:00 2020-03-02 | 09:00:00 | 09:00:00跑步
2020-03-04 | 04:30:00 | 04:30:00 2020-03-04 | 09:00:00 | 09:00:00跑步
2020-03-04 | 11:30:00 | 11:30:00 2020-03-04 | 19:30:00 | 19:30:00跑步
2020-03-05 | 05:00:00 | 05:00:00 2020-03-05 | 11:15:00 | 11:15:00跑步
2020-03-05 | 12:30:00 | 12:30:00 2020-03-05 | 22:08:00 | 22:08:00跑步

缺失记录：

开始日期 |开始时间 |结束日期 |结束时间 |地位 
:--------- | :-------- | :--------- | :----- | :------
2020-03-02 | 00:00:00 | 00:00:00 2020-03-02 | 01:30:00 | 01:30:00停止了
2020-03-02 | 09:00:00 | 09:00:00 2020-03-04 | 04:30:00 | 04:30:00停止了
2020-03-04 | 09:00:00 | 09:00:00 2020-03-04 | 11:30:00 | 11:30:00停止了
2020-03-04 | 19:30:00 | 19:30:00 2020-03-05 | 05:00:00 | 05:00:00停止了
2020-03-05 | 11:15:00 | 11:15:00 2020-03-05 | 12:30:00 | 12:30:00停止了

演示db<>fiddle 此处

Assuming you're using MySQL 8+, you could use the LAG() function to compare the current record's start date/time with the previous record's end date/time. When there's a difference, use those values to create the missing time span:

Previous End Date/Time ==> New Start Date/Time
Current Start Date/Time ==> New End Date/Time

Query:

This query will return the missing records, which you can insert into your table if desired.

WITH cte AS (
   -- using single datetime value for simpler logic
   SELECT * 
      , LAG (STR_TO_DATE(CONCAT(EndDate, ' ', EndTime), '%Y-%m-%d %H:%i:%s'), 1, NULL) 
            OVER (ORDER BY EndDate, EndTime) AS PrevEndDateTime
      , STR_TO_DATE(CONCAT(StartDate, ' ', StartTime), '%Y-%m-%d %H:%i:%s') AS StartDateTime            
   FROM   YourTable
)
SELECT CAST( DATE_FORMAT(COALESCE(PrevEndDateTime, StartDate),'%Y-%m-%d') AS DATE ) AS StartDate
       , CAST( DATE_FORMAT(COALESCE(PrevEndDateTime, StartDate),'%H:%i:%s') AS TIME ) AS StartTime
       , StartDate AS EndDate
       , StartTime AS EndTime
       , 'stopped' AS Status
FROM   cte
WHERE  StartDateTime <> PrevEndDateTime
OR     PrevEndDateTime IS NULL

Test Data:

StartDate  | StartTime | EndDate    | EndTime  | status 
:--------- | :-------- | :--------- | :------- | :------
2020-03-02 | 01:30:00  | 2020-03-02 | 09:00:00 | running
2020-03-04 | 04:30:00  | 2020-03-04 | 09:00:00 | running
2020-03-04 | 11:30:00  | 2020-03-04 | 19:30:00 | running
2020-03-05 | 05:00:00  | 2020-03-05 | 11:15:00 | running
2020-03-05 | 12:30:00  | 2020-03-05 | 22:08:00 | running

Missing Records:

StartDate  | StartTime | EndDate    | EndTime  | Status 
:--------- | :-------- | :--------- | :------- | :------
2020-03-02 | 00:00:00  | 2020-03-02 | 01:30:00 | stopped
2020-03-02 | 09:00:00  | 2020-03-04 | 04:30:00 | stopped
2020-03-04 | 09:00:00  | 2020-03-04 | 11:30:00 | stopped
2020-03-04 | 19:30:00  | 2020-03-05 | 05:00:00 | stopped
2020-03-05 | 11:15:00  | 2020-03-05 | 12:30:00 | stopped

demo db<>fiddle here

回复收藏 0 原文

~没有更多了~