2

The question is in the end of this post.

I found this link to generate date Generating a series of dates and modified it to generate time in an intervall:

SET @start_time = "08:02";
SET @stop_time  = "17:02";

SELECT
     TIME_FORMAT(time(CONCAT(m3, m2, ':', m1, m0)) , "%H:%i") as 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) AS m1,
    (SELECT 0 m2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS m2,
    (SELECT 0 m3 UNION SELECT 1 UNION SELECT 2) AS m3
where time(CONCAT(m3, m2, ':', m1, m0)) is not null 
and time(CONCAT(m3, m2, ':', m1, m0)) >= @start_time
and time(CONCAT(m3, m2, ':', m1, m0)) <= @stop_time
order by Time asc

This will generate this table:

Time
10:02
10:03
10:04
10:05
10:06
...
16:59
17:00
17:01

Question?

Can this be written in a more effective way? I should like to specify an interval with steps, so can quickly update it to every X minute intervall instead. I don't want to store the result in DB, i just want to generate it on the fly. Since I will join it with other tables.

I use this a lot in mysql queries to make time serie for measurements that are easy to plot in Excel.

GMB
  • 216,147
  • 25
  • 84
  • 135
banankontakt
  • 198
  • 9
  • 1
    I personally think there is a reasonable argument for storing it in the DB, as a table with 1440 rows, a time column, possibly with an hour column and minute column too then you can do eg `SELECT * FROM times WHERE MOD(minute, 5) = 0` (or `MOD(MINUTE(time), 5)` if no separated minutes column) to get eg "every 5 minutes, times ending with 0 / 5". Databases are really good at querying tables; they do a lot of it.. – Caius Jard Sep 02 '20 at 19:02
  • I only have a read only account, and my time series is often very short with maybe only usually under an hour. I made it longer in this example so the solution should be more generic. – banankontakt Sep 02 '20 at 19:09
  • You'll have to go some reasonable distance to find a DBA that would advocate generating X rows on the fly Y times a day over having a small amount of rows in a table and selecting them, I think.. The read only restriction is a bigger stick than the low number of rows is a carrot – Caius Jard Sep 02 '20 at 19:26
  • Adding a lot of temporary tables will pollute the database. Thats why they started with the read only account, since most of the users where bad at cleaning up after them self. – banankontakt Sep 02 '20 at 19:38
  • 1
    This isn't a temporary table, it's a permanent one; ask the next 10 DBA you come across what they think about calendar and numbers tables. They're probably more widespread than you think and they make a lot of sense, especially calendar tables, as they can tell you things that formulaic generation of date series can't, such as public holidays – Caius Jard Sep 02 '20 at 19:41
  • Easy to do with a sequence table on the fly in MariaDB. – Rick James Sep 02 '20 at 20:21
  • Meanwhile, I second Caisus's permanent table. It could even have a million _integers_ and use integer and/or date arithmetic to create all the minutes in an arbitrary range (up to 1M). – Rick James Sep 02 '20 at 20:25

1 Answers1

2

I'm a fan of recursive CTEs for this purpose:

with recursive times as (
      select time('10:00:00') as time
      union all
      select time + interval 17 minute
      from times
      where time < time('17:00:00')
     )
select *
from times;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786