Timestamp generator
The timestamp_sequence() function may be used as a timestamp generator to
create data for testing. Pseudo-random steps can be achieved by providing a
random function to the
step argument. A seed value may be provided to a random function if the
randomly-generated step should be deterministic.
timestamp_sequence
-
timestamp_sequence(startTimestamp, step)generates a sequence oftimestampstarting atstartTimestamp, and incrementing by astepset as alongvalue in microseconds. Thisstepcan be either;-
a static value, in which case the growth will be monotonic, or
-
a randomized value, in which case the growth will be randomized. This is done using random value generator functions.
-
Arguments:
startTimestamp: is atimestamprepresenting the starting (i.e lowest) generated timestamp in the sequence.step: is alongrepresenting the interval between 2 consecutive generated timestamps inmicroseconds.
Return value:
Return value type is timestamp.
Examples:
SELECT x, timestamp_sequence(
to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
100000L)
FROM long_sequence(5);
| x | timestamp_sequence |
|---|---|
| 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 |
SELECT x, timestamp_sequence(
to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
rnd_short(1,5) * 100000L)
FROM long_sequence(5);
| x | timestamp_sequence |
|---|---|
| 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 |
generate_series
Rather than generating a fixed number of timestamps, you can instead generate timestamps in a range,
using generate_series.
The step can be provided in either microseconds, or in a period string, similar to SAMPLE BY.
The start and end values are interchangeable, and a negative step value can be used to
obtain the series in reverse order.
The series is inclusive on both ends.
Arguments:
There are two timestamp-generating variants of generate_series:
generate_series(start, end, step_period) - generate a series of timestamps between start and end
in periodic steps.
generate_series(start, end, step_micros) - generates a series of timestamps between start and end,
in microsecond steps.
Return value:
Return value type is timestamp.
Examples:
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 |
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 |
generate_series(
'2025-01-01T00:00:00Z'::timestamp,
'2025-01-01T00:05:00Z'::timestamp,
60_000_000
);
| 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 |
generate_series(
'2025-01-01T00:00:00Z'::timestamp,
'2025-01-01T00:05:00Z'::timestamp,
-60_000_000
);
| generate_series |
|---|
| 2025-01-01T00:05:00.000000Z |
| 2025-01-01T00:04:00.000000Z |
| 2025-01-01T00:03:00.000000Z |
| 2025-01-01T00:02:00.000000Z |
| 2025-01-01T00:01:00.000000Z |
| 2025-01-01T00:00:00.000000Z |