GENERATE_SERIES()

Function Description

GENERATE_SERIES() can generate a sequence from the start value to the end value, which is usually applied to the following scenarios:

1. Generate continuous numbers: used to generate a series of integers, such as 1 to 10.
2. Generate date and time series: You can generate time series with intervals of every day and every hour.
3. Combined with table query: used to dynamically generate or extend data.

Function Syntax

>select * from generate_series(start, stop [, step]) g

Parameter definition

Parameters	Description
start	start value
stop	end value
step	step size, default is 1

Example

• Example 1:

-- Generate 1-5 integer sequences
mysql> select * from generate_series(5) g;
+---------+
| result |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---------+
5 rows in set (0.01 sec)

-- Generate 2-5 integer sequences
mysql> select * from generate_series(2, 5) g;
+---------+
| result |
+---------+
| 2 |
| 3 |
| 4 |
| 5 |
+---------+
4 rows in set (0.00 sec)

-- Generate a sequence of 1-5 integers and specify a step size of 2
mysql> select * from generate_series(1, 5,2) g;
+---------+
| result |
+---------+
| 1 |
| 3 |
| 5 |
+---------+
3 rows in set (0.01 sec)

--Generate date sequence
mysql> select * from generate_series('2020-02-28 00:00:00','2021-03-01 00:01:00', '1 year') g;
+--------------------------+
| result |
+--------------------------+
| 2020-02-28 00:00:00 |
| 2021-02-28 00:00:00 |
+--------------------------+
2 rows in set (0.00 sec)