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 of timestamp starting at startTimestamp, and incrementing by a step set as a long value in microseconds. This step can 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 a timestamp representing the starting (i.e lowest) generated timestamp in the sequence.
  • step: is a long representing the interval between 2 consecutive generated timestamps in microseconds.

Return value:

Return value type is timestamp.

Examples:

Monotonic timestamp increase
SELECT x, timestamp_sequence(
to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
100000L)
FROM long_sequence(5);
xtimestamp_sequence
12019-10-17T00:00:00.000000Z
22019-10-17T00:00:00.100000Z
32019-10-17T00:00:00.200000Z
42019-10-17T00:00:00.300000Z
52019-10-17T00:00:00.400000Z
Randomized timestamp increase
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);
xtimestamp_sequence
12019-10-17T00:00:00.000000Z
22019-10-17T00:00:00.100000Z
32019-10-17T00:00:00.600000Z
42019-10-17T00:00:00.900000Z
52019-10-17T00:00:01.300000Z