(Newbie Question) Interval Syntax Question / Explanation of Error
969094Oct 17 2012 — edited Oct 18 2012Ok. 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!