Share this page: Follow me to be notified of new articles:

mysql_generate_series() for MySQL (replica of PostgreSQL's generate_series() functions)

Introduction

Series of numbers mysql_generate_series() is a replica of PostgreSQL's generate_series() functions.
It allows you to create series of numbers (integer & bigint) or dates.

5 stored procedures are available:
  1. generate_series(first, last): return a series from "first" to "last" with an increment of 1
  2. generate_series_n(first, last, n): return a series from "first" to "last" with an increment of "n"
  3. generate_series_date_minute(first, last, n): return a series of datetime with an increment of "n" minutes
  4. generate_series_date_hour(first, last, n): return a series of datetime with an increment of "n" hours
  5. generate_series_date_day(first, last, n): return a series of datetime with an increment of "n" days

Try it yourself

You can try all the methods yourself after downloading the source code below.

Source code

You can find the source code on Github:
Watch Star Fork Download

Examples with integers/bigint

Here are a few examples showing how pg_IdFromDate() works:
-- Generate a series from A thru B
mysql> CALL generate_series(1, 10);
+--------+
| series |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
|     10 |
+--------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

-- Generate a series from A thru B with an interval of X
mysql> CALL generate_series_n(0, 10, 2);
+--------+
| series |
+--------+
|      0 |
|      2 |
|      4 |
|      6 |
|      8 |
|     10 |
+--------+
6 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Examples with dates / times

-- Generate a series with an interval of X minutes between 2 dates
mysql> CALL generate_series_date_minute('2015-09-03 00:00:00', '2015-09-03 00:20:00', 1);
+---------------------+
| series              |
+---------------------+
| 2015-09-03 00:00:00 |
| 2015-09-03 00:01:00 |
| 2015-09-03 00:02:00 |
| 2015-09-03 00:03:00 |
| 2015-09-03 00:04:00 |
| 2015-09-03 00:05:00 |
| 2015-09-03 00:06:00 |
| 2015-09-03 00:07:00 |
| 2015-09-03 00:08:00 |
| 2015-09-03 00:09:00 |
| 2015-09-03 00:10:00 |
| 2015-09-03 00:11:00 |
| 2015-09-03 00:12:00 |
| 2015-09-03 00:13:00 |
| 2015-09-03 00:14:00 |
| 2015-09-03 00:15:00 |
| 2015-09-03 00:16:00 |
| 2015-09-03 00:17:00 |
| 2015-09-03 00:18:00 |
| 2015-09-03 00:19:00 |
| 2015-09-03 00:20:00 |
+---------------------+
21 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

-- Generate a series with an interval of X hours between 2 dates
mysql> CALL generate_series_date_hour('2015-09-03', '2015-09-04', 1);
+---------------------+
| series              |
+---------------------+
| 2015-09-03 00:00:00 |
| 2015-09-03 01:00:00 |
| 2015-09-03 02:00:00 |
| 2015-09-03 03:00:00 |
| 2015-09-03 04:00:00 |
| 2015-09-03 05:00:00 |
| 2015-09-03 06:00:00 |
| 2015-09-03 07:00:00 |
| 2015-09-03 08:00:00 |
| 2015-09-03 09:00:00 |
| 2015-09-03 10:00:00 |
| 2015-09-03 11:00:00 |
| 2015-09-03 12:00:00 |
| 2015-09-03 13:00:00 |
| 2015-09-03 14:00:00 |
| 2015-09-03 15:00:00 |
| 2015-09-03 16:00:00 |
| 2015-09-03 17:00:00 |
| 2015-09-03 18:00:00 |
| 2015-09-03 19:00:00 |
| 2015-09-03 20:00:00 |
| 2015-09-03 21:00:00 |
| 2015-09-03 22:00:00 |
| 2015-09-03 23:00:00 |
| 2015-09-04 00:00:00 |
+---------------------+
25 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

-- Generate a series with an interval of X days between 2 dates
mysql> CALL generate_series_date_day('2015-09-03', '2015-09-05', 1);
+---------------------+
| series              |
+---------------------+
| 2015-09-03 00:00:00 |
| 2015-09-04 00:00:00 |
| 2015-09-05 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

How to insert rows in a table from a series

MySQL does not allow inserting data directly from the output of a stored procedure.
mysql_generate_series uses a temporary table called "series_tmp" for every procedure which you can use to insert a series in a table.

The following example shows how to insert multiple rows in MySQL tables easily:
-- Create a test table
mysql> CREATE TABLE test (a int, b text);
Query OK, 0 rows affected (0.01 sec)

-- Generate a series from 1 to 10
mysql> CALL generate_series(1, 10);
+--------+
| series |
+--------+
|      1 |
|      2 |
[........]
|      9 |
|     10 |
+--------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

-- Insert all the rows from the series into the test table
mysql> INSERT INTO test (a, b) SELECT series, 'This is a test' FROM series_tmp;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

-- Display the test table content
mysql> SELECT * FROM test;
+------+----------------+
| a    | b              |
+------+----------------+
|    1 | This is a test |
|    2 | This is a test |
|    3 | This is a test |
|    4 | This is a test |
|    5 | This is a test |
|    6 | This is a test |
|    7 | This is a test |
|    8 | This is a test |
|    9 | This is a test |
|   10 | This is a test |
+------+----------------+
10 rows in set (0.00 sec)

How to use a series within another query

As for inserts, it is not possible with MySQL to use the output of a stored procedure within another query.
We can again use mysql_generate_series's temporary table "series_tmp" to use the series with a JOIN in a SELECT query.

This example demonstrates how to display all hours from a date and their eventual associated row in another table:
-- Create test table
mysql> CREATE TABLE test2 (a datetime, b text);
Query OK, 0 rows affected (0.03 sec)

-- Insert 2 lines
mysql> INSERT INTO test2 (a, b) VALUES ('2015-09-03 02:00:00', 'Line 1'),
    ->                                 ('2015-09-03 14:00:00', 'Line 2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- Generate a series of each hour of the day
mysql> CALL generate_series_date_hour('2015-09-03 00:00:00', '2015-09-03 23:00:00', 1);
+---------------------+
| series              |
+---------------------+
| 2015-09-03 00:00:00 |
| 2015-09-03 01:00:00 |
[.....................]
| 2015-09-03 22:00:00 |
| 2015-09-03 23:00:00 |
+---------------------+
24 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

-- Select "test2" content
mysql> SELECT * FROM test2;
+---------------------+--------+
| a                   | b      |
+---------------------+--------+
| 2015-09-03 02:00:00 | Line 1 |
| 2015-09-03 14:00:00 | Line 2 |
+---------------------+--------+
2 rows in set (0.00 sec)

-- Select "test2" content with all hours of the day
mysql> SELECT series_tmp.series, test2.a, test2.b
    -> FROM series_tmp
    -> LEFT JOIN test2 ON series_tmp.series = test2.a
    -> ORDER BY series_tmp.series;
+---------------------+---------------------+--------+
| series              | a                   | b      |
+---------------------+---------------------+--------+
| 2015-09-03 00:00:00 | NULL                | NULL   |
| 2015-09-03 01:00:00 | NULL                | NULL   |
| 2015-09-03 02:00:00 | 2015-09-03 02:00:00 | Line 1 |
| 2015-09-03 03:00:00 | NULL                | NULL   |
| 2015-09-03 04:00:00 | NULL                | NULL   |
| 2015-09-03 05:00:00 | NULL                | NULL   |
| 2015-09-03 06:00:00 | NULL                | NULL   |
| 2015-09-03 07:00:00 | NULL                | NULL   |
| 2015-09-03 08:00:00 | NULL                | NULL   |
| 2015-09-03 09:00:00 | NULL                | NULL   |
| 2015-09-03 10:00:00 | NULL                | NULL   |
| 2015-09-03 11:00:00 | NULL                | NULL   |
| 2015-09-03 12:00:00 | NULL                | NULL   |
| 2015-09-03 13:00:00 | NULL                | NULL   |
| 2015-09-03 14:00:00 | 2015-09-03 14:00:00 | Line 2 |
| 2015-09-03 15:00:00 | NULL                | NULL   |
| 2015-09-03 16:00:00 | NULL                | NULL   |
| 2015-09-03 17:00:00 | NULL                | NULL   |
| 2015-09-03 18:00:00 | NULL                | NULL   |
| 2015-09-03 19:00:00 | NULL                | NULL   |
| 2015-09-03 20:00:00 | NULL                | NULL   |
| 2015-09-03 21:00:00 | NULL                | NULL   |
| 2015-09-03 22:00:00 | NULL                | NULL   |
| 2015-09-03 23:00:00 | NULL                | NULL   |
+---------------------+---------------------+--------+
24 rows in set (0.00 sec)

How to use generate_series for MySQL within a PHP script

It can get tricky to use MySQL stored procedures from a PHP script as MySQL needs to free the result outputted by the stored procedure before you can execute more queries.
To simplify this, all stored procedures have an equivalent ending in "_no_output" that do not generate any output but stores the series in a temporary table called "series_tmp".
For example, for "generate_series(first, last)" it will be "generate_series_no_output(first, last)".
More information and detailed examples are available on Github.

Conclusion

This tool tries to provide a simple replica to PostgreSQL's generate_series() functions within the limits of what is available with MySQL stored procedures.
You can view and download the project on Github.
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.