This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.
When working with data that is somehow related to dates and times you may need to generate a sequence of date to group by or select from.
The trick here is to use a recursive CTE (Common Table Expression. It can be a bit tricky to read but what the CTE does is to make a union with one additional datetime for each recursion. More examples can be found in the MariaDb documentation. And the syntax is very similar to the SQL Server syntax as you can see here.
The code below will create a sequence of datetimes with 10 minutes interval between each datetime value.
Like recursion in general, this can take up a lot of resources and be slow so I would not recommend using it in production code with high number of execution. But for data analysis and drilling into your data this trick can be very useful.
SET @stepSizeInMinutes = 10; -- Change this line to change the time interval SET @from = '2017-01-01 00:00:00.00000'; SET @to = '2017-01-04 23:50:00.00000'; -- Create Recursive Discrete Table WITH RECURSIVE Recursive_CTE AS ( SELECT @from AS TimestampUtc UNION ALL SELECT TimestampUtc + INTERVAL @stepSizeInMinutes MINUTE FROM Recursive_CTE WHERE TimestampUtc < @to ) SELECT * FROM Recursive_CTE ORDER BY TimestampUtc
As mentioned in the comments by @darkain, thanks for the tip, the date sequence can be created by using the MariaDB Sequence engine and this solution is a lot more affective than using the recursive CTE. So here is the SQL for that:
SET @stepSizeInMinutes = 10; -- Change this line to change the time interval SET @from = '2017-01-01 00:00:00.00000'; SET @to = '2017-01-04 23:50:00.00000'; SELECT @from + INTERVAL seq * @stepSizeInMinutes MINUTE AS `Datetime sequence` FROM seq_0_to_99999999 WHERE seq BETWEEN 0 and (SELECT TIMESTAMPDIFF(MINUTE, @from, @to) / @stepSizeInMinutes);