CODEWITHSUNDEEP
sql
Blackdynomite
Blackdynomite

Reputation: 431

Take only Days from Interval Column - Postgresqk

I am trying to take only the days value from the below interval type column.

To do so, I ran the following query but all it did was round everything to days as shown in the table below:

select date_trunc('days', avg(d.date)) as Days_till_invite
from datamodel d

0 years 0 mons 173 days 0 hours 0 mins 0.00 secs
0 years 0 mons 173 days 0 hours 0 mins 0.00 secs
0 years 0 mons 173 days 0 hours 0 mins 0.00 secs
0 years 0 mons 173 days 0 hours 0 mins 0.00 secs
0 years 0 mons 173 days 0 hours 0 mins 0.00 secs
0 years 0 mons 172 days 0 hours 0 mins 0.00 secs
0 years 0 mons 172 days 0 hours 0 mins 0.00 secs
0 years 0 mons 172 days 0 hours 0 mins 0.00 secs
0 years 0 mons 172 days 0 hours 0 mins 0.00 secs
0 years 0 mons 172 days 0 hours 0 mins 0.00 secs
0 years 0 mons 171 days 0 hours 0 mins 0.00 secs
0 years 0 mons 171 days 0 hours 0 mins 0.00 secs
0 years 0 mons 171 days 0 hours 0 mins 0.00 secs
0 years 0 mons 170 days 0 hours 0 mins 0.00 secs
0 years 0 mons 170 days 0 hours 0 mins 0.00 secs

Desired Output

Ideally, the output should just be: (only extracting the day value)

173
173
173
173
172 
[...]

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Probably the simplest way uses epoch:

select extract(epoch from d.date) / (24 * 60 * 60) as days

Upvotes: 1

Related Questions