如何在我的 sql 数据库中的两个时间戳记录之间按分钟查找所有时间戳值间隔 [英] How to find all the timestamp values interval by each minute between the two timestamp records in my sql database

问题描述

我的 mysql 数据库中有一个包含三个字段 Id (Integer) - Unique, Open Date (Datetime), Close Date(DateTime) 的表:

I have a table having three fields Id (Integer) - Unique, Open Date (Datetime), Close Date(DateTime) in my mysql database:

Id  Open Date                  Close Date
1   2019-07-03 16:28:39.497    2019-07-04 16:28:39.497
2   2019-07-04 15:28:39.497    2019-07-05 19:28:39.497
…..N        

我想以每分钟为间隔计算打开日期和关闭日期之间的所有时间戳.

I want to calculate the all the timestamps between open date and close date with an interval of each minute.

所以我想要的最终输出是这样的:

So the final output I want is like this:

Id  Open Date                 Close Date                TimeStamp Range
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:29:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:30:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:31:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   …..........................
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-04 16:27:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:29:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:30:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:31:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   ….................................
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-05 19:27:00.0000
N   …............................   …...........................    …......................................

有人能帮我写一个在我的 sql 中支持的查询吗?

Would someone help me to write the query for this that will be supported in my sql?

推荐答案

借助 https://stackoverflow.com/a/45992247/7616138 在 MySQL 中生成一个系列我摆弄产生这个:

With the help of https://stackoverflow.com/a/45992247/7616138 to generate a series in MySQL I fiddled around to produce this:

假设您的表名为 entries，以下查询会生成您要求的结果.请注意，此查询效率不高，因为它使用交叉连接来构建分钟系列.如果您的间隔更大，您可能需要扩展交叉连接.

Assuming your table is called entries, the following query produces the result you asked for. Please not, that this query is not very efficient as it uses cross joins to build up the series of minutes. If your intervals are bigger, you may need to extend the cross joins.

SELECT 
    a.*, 
    DATE_ADD(DATE_SUB(a.open_date, INTERVAL SECOND(a.open_date) SECOND), INTERVAL gen_time MINUTE) AS gen_date_time
FROM 
    entries AS a
    LEFT JOIN
        (
            SELECT
                m3 * 1000 + m2 * 100 + m1 * 10 + m0 AS gen_time
            FROM
                (SELECT 0 m0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m0,
                (SELECT 0 m1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m1,
                (SELECT 0 m2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m2,
                (SELECT 0 m3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m3
            ORDER BY
                gen_time asc
        ) AS b ON (DATE_ADD(a.open_date, INTERVAL gen_time MINUTE) <= a.close_date)
ORDER BY
    a.id,
    gen_date_time

说明:

每一行像 SELECT 0 m0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 产生从0到9的数字. 将这些行中的几条交叉连接在一起给出了这些数字的每一种可能组合.我们使用每一行来生成要添加到 open_date 的特定分钟数数字(m3 * 1000 + m2 * 100 + m1 * 10 + m0).这一系列的分钟被加入到条目表中，只使用适合间隔的分钟数 (DATE_ADD(a.open_date, INTERVAL gen_time MINUTE) <= a.close_date).在 SELECT 中，open_date 四舍五入到分钟 (DATE_SUB(a.open_date, INTERVAL SECOND(a.open_date) SECOND)) 和添加分钟数 (DATE_ADD(..., INTERVAL gen_time MINUTE)).

Each line like SELECT 0 m0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 produces the numbers from 0 to 9. Cross joining several of these lines together gives every possible combination of these numbers. We use each line to produce a specific digit of the number of minutes to add to open_date (m3 * 1000 + m2 * 100 + m1 * 10 + m0). This series of minutes is joined to the entries table using only as many minutes as fit in the interval (DATE_ADD(a.open_date, INTERVAL gen_time MINUTE) <= a.close_date). In the SELECT the open_date is rounded to the minute (DATE_SUB(a.open_date, INTERVAL SECOND(a.open_date) SECOND)) and the number of minutes is added (DATE_ADD(..., INTERVAL gen_time MINUTE)).

假设的架构和示例数据:

Assumed schema and sample data:

CREATE TABLE entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  open_date TIMESTAMP,
  close_date TIMESTAMP
);

INSERT INTO entries (open_date, close_date) VALUES
  ("2019-07-03 16:28:39.497", "2019-07-04 16:28:39.497"),
  ("2019-07-04 15:28:39.497", "2019-07-05 19:28:39.497");

这篇关于如何在我的 sql 数据库中的两个时间戳记录之间按分钟查找所有时间戳值间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！