如何在我的 sql 数据库中的两个时间戳记录之间按分钟查找所有时间戳值间隔

本文介绍了如何在我的 sql 数据库中的两个时间戳记录之间按分钟查找所有时间戳值间隔的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的 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?

推荐答案

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

With the help of stackoverflow/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");