Postgresql: How to find difference between 2 dates and times in decimal hours?

1
votes

System: Postgresql 9.6. I didn't design this system and this table has startdate (Date type field), starttime (Time), enddate (Date) and endtime (Time) fields but not hours elapsed between the 2. The field types for each field above are in (parens).

What I need: using Date and Time fields, I would like the difference in decimal hours. This would be a calculated column in my SELECT statement.

  1. I've searched a search engine and Stackoverflow for this and there is nothing that exactly matches what I need.
  2. Some examples show me the difference in days, but that's now what I want.
  3. Other example show only the difference in 2 times, but I need to account for 3rd shift workers that start on one day and end on the next day.
  4. Other examples do not take into account the different stardate and enddate.
  5. Some people may start on one day and finish their shift on the next day so this must take 2 different dates into account.

I tried this and got the result in "hh:mm:ss" format: endtime - starttime as hrs

I've been searching a while how to convert that result to decimal hours. I've also tried these formats: 

(date enddate time endtime) - (date startdate time starttime) as hrs

(enddate endtime) - (startdate starttime) as hrs

timestamp(enddate endtime) - timestamp(startdate starttime) as hrs

age(enddate endtime) - age(startdate starttime) as hrs

extract(EPOCH FROM timestamptz(enddate endtime)) - extract(EPOCH from timestamptz (startdate starttime))

I don't do much SQL at my job so any help would be appreciated. I learn by seeing code but I'd also like to understand what each part does so I can UNDERSTAND and apply that understanding to future issues.

Thank you!

2
sqlpostgresqldatetimecalculated-columns

2 Answers

2
votes
select extract (EPOCH from (enddate + endtime - (startdate + starttime))) / (60 * 60) from a;
0
votes

I think this will does what you want:

select ((extract(epoch from enddate) + extract(epoch from endtime) -
        (extract(epoch from startdate) + extract(epoch from starttime)
       ) / (60.0 * 60)