CODEWITHSUNDEEP
postgresql
Eugen Konkov
Eugen Konkov

Reputation: 25223

How to calculate how many intervals at given daterange? simpler version

I can write:

select count(*) from generate_series( 
    '2019-03-01'::date, '2019-05-01'::date, 
    interval '3 day 1 hour' 
) 
-- exclude upper boundary
where generate_series <> date '2019-05-01'::date;

Is there a way to do it simpler? like:

daterange( '2019-03-01', '2019-05-01' ) / interval '3 day 1 hour'

Upvotes: 1

Views: 43

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

You can use

EXTRACT(epoch FROM some_interval)

to get an interval's duration in seconds.

You could use that as follows:

SELECT EXTRACT(epoch FROM '2019-05-01'::timestamptz - '2019-03-01'::timestamptz)
       / EXTRACT(epoch FROM interval '3 day 1 hour');

Note that this will only give correct answers for intervals that are measured in days or lesser units; for months and more you have to go with your original solution.

Upvotes: 1

Related Questions