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);
Top comments (2)
This can also be done without complex variables and CTEs by simply using the built-in "Sequence" engine in MariaDB!
mariadb.com/kb/en/sequence-storage...
Thanks for the tip!