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!

(Newbie Question) Interval Syntax Question / Explanation of Error

969094Oct 17 2012 — edited Oct 18 2012
Ok. So I'm reaching into a large ticketing system based on Oracle (11g 11.2.0.1.0 64bit) where they are storing the "ticket-open-time", the "ticket-resolved-time" and the interval "time-to-repair". Originally I wanted to compute the number of minutes of the time-to-repair value. 6 hours and countless Google searches later, I'm no closer to the proper syntax. I was able to work around the problem via simple math based on the open/resolved values. What I can't figure out is why I can't apply the same extract-syntax to just the interval field.

Here's a simplified query that works:

SELECT
TO_CHAR(OpenTimeGMT, 'MM/DD/YYYY HH24:MI:SS') "OT"
,TO_CHAR(ResolvedTimeGMT, 'MM/DD/YYYY HH24:MI:SS') "RT"
,TO_CHAR(TimeToRepairGMT, 'MM/DD/YYYY HH24:MI:SS') "TTR"
,extract (day from (ResolvedTimeGMT- OpenTimeGMT) day to second) "Days"
,extract (hour from (ResolvedTimeGMT- OpenTimeGMT) day to second) "Hours"
,extract (minute from (ResolvedTimeGMT- OpenTimeGMT) day to second) "Minutes"
,(extract (day from (ResolvedTimeGMT- OpenTimeGMT) day to second) ) * 24 * 60 +
(extract (hour from (ResolvedTimeGMT- OpenTimeGMT) day to second) ) * 60 +
extract (minute from (ResolvedTimeGMT- OpenTimeGMT) day to second) "TotalMinutes"
FROM Wherever

Which produces:
[OT] 09/24/2012 20:06:29
[RT] 09/26/2012 13:56:13
[TTR] 01/02/4000 17:49:44 --- Note the year.
[Days] 1
[Hours] 17
[Minutes] 49
[TotalMinutes] 2509

The issue:
When I write [extract (minute from (ResolvedTimeGMT- OpenTimeGMT) day to second) "Minutes"], I get 49 minutes as I expect

When I write [extract (minute from (TimeToRepairGMT) day to second) "Minutes"], I get an error "syntax error was found in interval value expression"

All three fields are declared as "date" per the DESC command (even though TimeToRepairGMT is clearly an interval, based on the value of "01/02/4000 17:49:44"). While I can plow along with my work-around, I'd really like to know how to deal with intervals that are stored in the Oracle tables as dates. Any help is appreciated!
This post has been answered by Frank Kulash on Oct 17 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 15 2012
Added on Oct 17 2012
13 comments
799 views