Postgres range select between timestamps

Question

Having two timestamps, and result in minutes between them lets say 320 minutes I need to calculate full hours, lets say we have here 5h and 20 minutes and I need to insert 6 rows with minutes column (5 rows with 60 as minutes column and last one with 20 minutes)

What is best way to do it in Postgres, some loops or trying to select numbers with cte?

S-Man · Accepted Answer · 2019-01-07 14:56:59Z

1

demo:db<>fiddle

WITH timestamps AS (
    SELECT '2019-01-07 03:30:00'::timestamp as ts1, '2019-01-07 08:50:00'::timestamp as ts2
)

SELECT 60 as minutes
FROM timestamps, generate_series(1, date_part('hour', ts2 - ts1)::int)

UNION ALL

SELECT date_part('minute', ts2 - ts1)::int
FROM timestamps

date_part extracts the hour (or minute) value from the timestamp difference.
with the generate_series function I am generating n rows with value 60 (n = hours)
Adding the remaining minutes with UNION ALL

Edit: For more than 1 day:

Instead of date_part use EXTRACT(EPOCH FROM ...) which gives you the difference in seconds.

WITH timestamps AS (
    SELECT '2019-01-06 03:30:00'::timestamp as ts1, '2019-01-07 08:50:00'::timestamp as ts2
)

SELECT 60 as minutes
FROM timestamps, generate_series(1, (EXTRACT(EPOCH FROM ts2 - ts1) / 60 / 60)::int)

UNION ALL

SELECT (EXTRACT(EPOCH FROM ts2 - ts1) / 60)::int % 60
FROM timestamps

Calculate the seconds into hours with / 60 / 60
Calculate the remaining seconds with / 60 % 60 (first step gives you the minutes, the modulo operator % gives you the remaining minutes to hour)

edited Jan 7, 2019 at 14:56

answered Jan 7, 2019 at 14:31

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kosnkov Over a year ago

this is great answer but the only problem is that if I chnage one timestamp to be other date like 2 days later then the difference is not calculated, the difference can't calculate something like total hours

S-Man Over a year ago

@kosnkov added the solution for your case, updated fiddle

Collectives™ on Stack Overflow

Postgres range select between timestamps

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related