CODEWITHSUNDEEP
sqlpostgresqltimestampintervals
Omer Freundlich
Omer Freundlich

Reputation: 23

Postgres Interval Returns Decimal Numbers

I'm trying to calculate interval between 2 timestamp fields and to get the result with numeric numbers instead decimal.

I already had this issue on 'seconds' field and I resolved it with 'date_trunc', but now I have it again on 'hour' field.

ex: date_trunc('seconds', (time1 - time2)) = 3.20:54:32

Upvotes: 1

Views: 576

Answers (1)

Belayer
Belayer

Reputation: 14934

The subtraction of 2 timestamps produces an interval (as you have already seen). Then you can extract the individual components from the resulting interval and apply the necessary multiplication to convert to the desired period: So:

with times (t2, t1 ) as 
     (values ('20220315 23:53:34'::timestamp, '20220312 02:59:02'::timestamp)  )
     
select elapsed
     , round( ( extract('days'   from elapsed) * 24  
              + extract('hour'   from elapsed)  
              + extract('minute' from elapsed) / 60.0 
              + extract('second' from elapsed) / 3600.0 
              ) 
           , 2) total_hours
  from ( select t2 - t1  elapsed from times) duration;

Result:

+-----------------+-------------+
|     elapsed     | total_hours |
+-----------------+-------------+
| 3 days 20:54:32 | 92.91       |
+-----------------+-------------+

Upvotes: 1

Related Questions