Question 285923 in Mysql Question posted in
The official MySQL documentation can be found here.

How to retrieve each minute per row b/w two timestamps in MySQL

I would require your kind assistance in a query to fetch each minute per row b/w two dates in MySQL.
(this is part of a larger analysis that requires per-minute aggregation of stats. Group by would only work when that minute is available in the timestamps but I want all of the in b/w minutes as well)

e.g:
Given: start_date: 2022-10-01 20:00:00, end_date: 2022-10-01 21:00:00

Output:

.............................
date_min_col
.............................
2022-10-01 20:01:00
2022-10-01 20:02:00
2022-10-01 20:03:00
2022-10-01 20:04:00
2022-10-01 20:05:00
.
.
.
2022-10-01 21:00:00
Tags:

2

Answers


  1. 0

    I hope this query helps you:

    WITH RECURSIVE MinuteList AS (
  SELECT '2022-10-01 20:00:00' AS minute_date
  UNION ALL
  SELECT minute_date + INTERVAL 1 MINUTE
  FROM MinuteList
  WHERE minute_date + INTERVAL 1 MINUTE <= '2022-10-01 21:00:00'
)
SELECT minute_date
FROM MinuteList;

    It will return something like this:

    enter image description here

    Login or Signup to reply.
  2. 0

    If CTE is not supported then

    SELECT @startdate + INTERVAL n1.n * 10 + n2.n + 1 MINUTE timepoint
FROM ( SELECT 0 n UNION SELECT 1 UNION ... SELECT 5) n1
JOIN ( SELECT 0 n UNION SELECT 1 UNION ... SELECT 9) n2
HAVING timepoint <= @enddate

    The amount of synthetic tables and their sizes must allow to generate the numbers not less than the timerange width.

    Also you may use service numbers table created in service database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search