MariaDB Quick-tip #5 - Create a sequence of date and time

#sql #mysql #mariadb

MariaDB tips and tricks

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.

Create a sequence of date and time

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

Create the sequence using MariaDB Sequence engine

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);