How to get a list of hours between two timestamp?

Question

I want to get all hours between two timestamps.

For example from 2023-02-23 14:38 to 2023-02-23 19:32

The result should be

14 
15
16
17
18
19

better i get also additional the minutes in the calculated hour

Hour	Minutes
14	38
15	60
16	60
17	60
18	60
19	32

Is there a function in postgresql for this query ?

Answer 1

The simple solution to get the hours:

SELECT
    extract(hour FROM a) AS hour
FROM
    generate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),
                    date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour') 
AS t (a);

hour 
------
   14
   15
   16
   17
   18
   19

UPDATED to include minutes.

SELECT
    extract(hour FROM a) AS hour,
    CASE WHEN a = '2023-02-23 14:00'::timestamp THEN
        extract(minute from'2023-02-23 14:38'::timestamp - a)
    WHEN a = '2023-02-23 19:00'::timestamp THEN
        extract(minute from '2023-02-23 19:32'::timestamp - a)
    ELSE
        to_char(a - lag(a) over(), 'HH')::integer * 60
    END AS minutes
FROM
    generate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),
    date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour') AS t (a);

hour | minutes 
------+---------
   14 |      38
   15 |      60
   16 |      60
   17 |      60
   18 |      60
   19 |      32