SQL question - average timestamp oracle

building semantic layer
1

Hey peoples,

I need to find the sql to take a timestamp field and get then find the average time in HH:MM format. I’m using Oracle.

Timestamp
12:26
12:28
16:25

Can anyone help?
Thanks!

khaleen :uk: (BOB member since 2005-05-05)

2

I am assuming the timestamp is a duration of some sort? What you will need to do is convert everything to numbers (so 11:30 becomes 11.5), do the average, and then convert the resulting average back to a time stamp.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Timestamp is an actual time not duration, i.e. 16.25 is 4:25pm.

I figure I have to do something like this:
1 - convert the hours to minutes: (12+12+16)60
2 - add on the minutes: +(26+28+25)
3 - equals 2479 minutes
4 - divide by the count of timestamps: /3
5 - divide by 60 to get hours = 13.77
6 - convert the decimal back to minutes: 77.6 = 46
7 - average is therefore 13.46

Does this logic sound correct or is there a better way of calculating this?

khaleen :uk: (BOB member since 2005-05-05)