Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Difference in Timestamps with millisec resolution

417276Mar 19 2004 — edited Mar 19 2004
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2004
Added on Mar 19 2004
3 comments
956 views