Dynamical table size on timestamp difference

August 29, 2022

Seeking help with a dynamic table response.
In javascript i create a variable Tdiff which is the absolut time difference (in hours) between two timestamps. For example 2022-04-04 12:00 and 2022-04-04 14:00 would return 2 as the variable value.

In SQL i want to use this to create a table with 3 rows, 2022-04-04 12:00, 2022-04-04 13:00 and 2022-04-04 14:00.

Pseudo code

    t1 timestamp without time zone,
    t2 timestamp without time zone,
    rownumber integer)
    RETURNS TABLE(time timestamp, speed integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS rownumber

AS $BODY$
BEGIN
SELECT * FROM table WHERE timestamp BETWEEN t1 AND t2 GROUP BY (HOUR FROM TIMESTMAP);
RETURN QUERY
END;
$BODY$;

>Solution :

This is simpel in PostgreSQL. They feature the GENERATE_SERIES function for this:

select *
from generate_series (timestamp '2022-04-04 12:00:00',
                      timestamp '2022-04-04 14:00:00', 
                      interval '1 hour');

Docs: https://www.postgresql.org/docs/current/functions-srf.html

Demo: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=92a2af64c54fa4b11b4c249b14dc8b43