This Gist helps to generate Prometheus Counter data behaviour on TimescaleDB/PostgresQL

generate_counter_data_postgres.md

Generate counter metric data

This example was done on TimescaleDB@2.8.1.

Create function

create or replace function generate_counter_data(_from timestamptz, _till timestamptz, _step interval, _insert_table_name TEXT) returns void as
$$
    declare
        r record;
        counter integer := 0;
    begin
        for r in
            select generate_series as ts from generate_series(
                    _from, _till, _step
            )
        loop
            EXECUTE FORMAT ('insert into %I values ($1::timestamptz, $2::bigint)', _insert_table_name) USING r.ts, counter;
            counter := counter + 1;
        end loop;
    end;
$$
language plpgsql;

Usage example

create table test_table ( time timestamptz not null, value bigint not null );

-- Usage example.
select generate_counter_data('2022-06-01 00:00:00'::timestamptz, '2022-10-20 00:00:00'::timestamptz, interval '30 seconds', 'test_table');