CODEWITHSUNDEEP
sqlpostgresqldatetimetimestampintervals
DIBits
DIBits

Reputation: 533

How to find out how many times a INTERVAL includes another one?

I have two TIMESTAMP and a INTERVAL. I want to know how many times the interval fits into the gap between the timestamps.

Subtracting two TIMESTAMP gives me an INTERVAL. 

SELECT TIMESTAMP '2019-01-30' -  TIMESTAMP '2019-01-29';

->
0 years 0 mons 1 days 0 hours 0 mins 0.00 secs

But I cant do a INTERVAL / INTERVAL division. This runs in an exception:

SELECT (TIMESTAMP '2019-01-30' -  TIMESTAMP '2019-01-29') / INTERVAL '1 hour';

I would like to have an integer at the end. 24 for the previous example. Is there a way to handle it in PostgreSQL?

Upvotes: 1

Views: 31

Answers (1)

user330315
user330315

Reputation:

You need to normalize both intervals, e.g. by extracting the number of seconds and then dividing them: 

SELECT extract(epoch from (TIMESTAMP '2019-01-30' -  TIMESTAMP '2019-01-29')) / extract(epoch from INTERVAL '1 hour');

Upvotes: 2

Related Questions