Sequence generators

These functions generate rows and sequences for creating test data. Use them together with random value generators to populate tables with test data.

Choosing the right function

Function	Use when...	Example
long_sequence(n)	You need exactly N rows	Generate 1 million test records
generate_series(start, end, step)	You need values in a specific range	Generate hourly timestamps for January 2025
timestamp_sequence(start, step)	You need microsecond timestamps with long_sequence (supports random steps)	Generate N rows with varying intervals
timestamp_sequence_ns(start, step)	You need nanosecond timestamps with long_sequence	Generate N rows with nanosecond precision

Key difference for timestamps:

• generate_series is standalone and range-based: "give me timestamps from A to B"
• timestamp_sequence requires long_sequence and is count-based: "give me N timestamps starting at A"

Function reference

• generate_series - generate arithmetic series (long, double, or timestamp)
• long_sequence - generate N rows for use with random functions
• timestamp_sequence - generate monotonically increasing microsecond timestamps
• timestamp_sequence_ns - generate monotonically increasing nanosecond timestamps

generate_series

Generates a pseudo-table containing an arithmetic series in a single column. Use it when you need a specific range of values rather than a specific number of rows.

You can call it in isolation (generate_series(...)) or as part of a SELECT statement (SELECT * FROM generate_series(...)).

The start and end values are interchangeable. Use a negative step value to obtain a descending series. The series is inclusive on both ends.

Variants:

• generate_series(start_long, end_long) - generates a series of longs with step 1.
• generate_series(start_long, end_long, step_long) - generates a series of longs with custom step.
• generate_series(start_double, end_double) - generates a series of doubles with step 1.
• generate_series(start_double, end_double, step_double) - generates a series of doubles with custom step.
• generate_series(start_timestamp, end_timestamp, step_period) - generates a series of timestamps with a period step.
• generate_series(start_timestamp, end_timestamp, step_micros) - generates a series of timestamps with microsecond step.

QuestDB determines which variant to use based on the argument types.

Arguments:

• start is the first value in the series (inclusive).
• end is the last value in the series (inclusive).
• step is the increment between values (default: 1 for numeric types).

For timestamp variants, step_period is a string using the format:

Unit	Example	Description
n	'500n'	nanoseconds
U	'100U'	microseconds
s	'30s'	seconds
m	'5m'	minutes
h	'1h'	hours
d	'1d'	days

Use negative values for descending series (e.g., '-1d').

Return value:

The column type of the pseudo-table matches the argument types: LONG, DOUBLE, TIMESTAMP, or TIMESTAMP_NS (if nanosecond timestamps are provided).

Examples:

Long series:

Ascending LONG seriesDemo this query

generate_series(-3, 3);

generate_series
-3
-2
-1
0
1
2
3

Descending LONG seriesDemo this query

generate_series(3, -3, -1);

generate_series
3
2
1
0
-1
-2
-3

Double series:

Ascending DOUBLE seriesDemo this query

generate_series(-3d, 3d);

generate_series
-3.0
-2.0
-1.0
0.0
1.0
2.0
3.0

Descending DOUBLE series with custom stepDemo this query

generate_series(3d, -3d, -1.5d);

generate_series
3.0
1.5
0.0
-1.5
-3.0

Timestamp series:

Timestamp series using a periodDemo this query

generate_series('2025-01-01', '2025-02-01', '5d');

generate_series
2025-01-01T00:00:00.000000Z
2025-01-06T00:00:00.000000Z
2025-01-11T00:00:00.000000Z
2025-01-16T00:00:00.000000Z
2025-01-21T00:00:00.000000Z
2025-01-26T00:00:00.000000Z
2025-01-31T00:00:00.000000Z

Descending timestamp seriesDemo this query

generate_series('2025-01-01', '2025-02-01', '-5d');

generate_series
2025-02-01T00:00:00.000000Z
2025-01-27T00:00:00.000000Z
2025-01-22T00:00:00.000000Z
2025-01-17T00:00:00.000000Z
2025-01-12T00:00:00.000000Z
2025-01-07T00:00:00.000000Z
2025-01-02T00:00:00.000000Z

Timestamp series using microsecondsDemo this query

generate_series(
    '2025-01-01T00:00:00Z'::timestamp,
    '2025-01-01T00:05:00Z'::timestamp,
    60_000_000 -- 1 minute in microseconds
);

generate_series
2025-01-01T00:00:00.000000Z
2025-01-01T00:01:00.000000Z
2025-01-01T00:02:00.000000Z
2025-01-01T00:03:00.000000Z
2025-01-01T00:04:00.000000Z
2025-01-01T00:05:00.000000Z

Nanosecond timestamp seriesDemo this query

generate_series(
    to_timestamp_ns('2025-01-01T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    '2025-01-01T00:00:00.000001',
    '500n'
);

generate_series
2025-01-01T00:00:00.000000000Z
2025-01-01T00:00:00.000000500Z
2025-01-01T00:00:00.000001000Z

long_sequence

Generates a pseudo-table with a specified number of rows. This is the primary function for creating test data in QuestDB.

• long_sequence(num_rows) - generates rows with a random seed.
• long_sequence(num_rows, seed1, seed2) - generates rows deterministically using the provided seed values.

The function serves two purposes:

1. Generates a pseudo-table with an ascending series of LONG numbers starting at 1 (accessible via column x)
2. Provides the seed for pseudo-randomness to all random value functions

tip

Deterministic procedural generation makes it easy to test on vast amounts of data without moving large files across machines. Using the same seed on any machine at any time will consistently produce the same results for all random functions.

Arguments:

• num_rows is a long representing the number of rows to generate.
• seed1 and seed2 are long numbers that combine into a long128 seed for deterministic generation.

Return value:

Returns a pseudo-table with a single column x of type long, containing values from 1 to num_rows.

Examples:

Generate rows with random values

SELECT x, rnd_double()
FROM long_sequence(5);

x	rnd_double
1	0.3279246687
2	0.8341038236
3	0.1023834675
4	0.9130602021
5	0.718276777

Access row number using the x column

SELECT x, x * x AS square
FROM long_sequence(5);

x	square
1	1
2	4
3	9
4	16
5	25

Deterministic generation with fixed seed

SELECT rnd_double()
FROM long_sequence(2, 128349234, 4327897);

note

The results below will be the same on any machine at any time as long as they use the same seed in long_sequence.

rnd_double
0.8251337821991485
0.2714941145110299

timestamp_sequence

Generates monotonically increasing microsecond timestamps when used with long_sequence(). Unlike generate_series, this function generates a single value per row and requires long_sequence() to produce multiple rows.

For nanosecond timestamps, use timestamp_sequence_ns.

• timestamp_sequence(startTimestamp, step) - generates timestamps starting at startTimestamp, incrementing by step microseconds per row.

The step can be:

• A fixed value, resulting in evenly-spaced timestamps
• A random function invocation (e.g., rnd_short(1, 5) * 100000L), resulting in timestamps that grow by random intervals

Arguments:

• startTimestamp is the starting (lowest) timestamp in the sequence.
• step is the interval in microseconds between consecutive timestamps.

Return value:

Return type is always TIMESTAMP (microsecond resolution), regardless of the input type.

Examples:

Evenly-spaced timestamps (100ms apart)

SELECT x, timestamp_sequence(
    to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    100000L -- 100ms in microseconds
) AS ts
FROM long_sequence(5);

x	ts
1	2019-10-17T00:00:00.000000Z
2	2019-10-17T00:00:00.100000Z
3	2019-10-17T00:00:00.200000Z
4	2019-10-17T00:00:00.300000Z
5	2019-10-17T00:00:00.400000Z

Random intervals between timestamps

SELECT x, timestamp_sequence(
    to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    rnd_short(1, 5) * 100000L -- 100-500ms random intervals
) AS ts
FROM long_sequence(5);

x	ts
1	2019-10-17T00:00:00.000000Z
2	2019-10-17T00:00:00.100000Z
3	2019-10-17T00:00:00.600000Z
4	2019-10-17T00:00:00.900000Z
5	2019-10-17T00:00:01.300000Z

timestamp_sequence_ns

Generates monotonically increasing nanosecond timestamps when used with long_sequence(). This is the nanosecond counterpart of timestamp_sequence.

• timestamp_sequence_ns(startTimestamp, step) - generates timestamps starting at startTimestamp, incrementing by step nanoseconds per row.

The step can be:

• A fixed value, resulting in evenly-spaced timestamps
• A random function invocation, resulting in timestamps that grow by random intervals

Arguments:

• startTimestamp is the starting (lowest) timestamp in the sequence. Use to_timestamp_ns() to create a nanosecond-resolution start value.
• step is the interval in nanoseconds between consecutive timestamps.

Return value:

Return type is always TIMESTAMP_NS (nanosecond resolution).

Examples:

Nanosecond timestamps (100ns apart)

SELECT x, timestamp_sequence_ns(
    to_timestamp_ns('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    100L -- 100 nanoseconds
) AS ts
FROM long_sequence(5);

x	ts
1	2019-10-17T00:00:00.000000000Z
2	2019-10-17T00:00:00.000000100Z
3	2019-10-17T00:00:00.000000200Z
4	2019-10-17T00:00:00.000000300Z
5	2019-10-17T00:00:00.000000400Z