Difference in Timestamps with millisec resolution
417276Mar 19 2004 — edited Mar 19 2004I have a table with a Timestamp column called start_time and a Timestamp column called stop_time. I need to calculate the average time difference between these columns across rows grouped by another column. Subtracting these two columns yields an Interval datatype. I cannot average an Interval. I tried doing a to_char conversion with a Julian date format, then doing a to_number on that result, but the Julian format when performed on a Timestamp datatype, will only give me the number of days, not the fractional portion, so I loose the number of hours, minutes, seconds and milliseconds.
I cannot use a Date datatype, as I need the millisecond resolution, which was the whole point of using the Timestamp datatype in the first place.
Is there any clean way of performing this calculation? Is it even possible?
Thank you in advance for your help.