interval datatype

[lyanch]

Hello all,

I hope this is the correct forum for my question. I am using standard date arithmetic:

END_TIME - START_TIME

Which returns an interval datatype.

I can't for the life of me figure out how to turn the interval that is returned into seconds as an int (or number I don't care.. just not an interval)

I am sure there is a simple solution out there.. but I can't seem to find it.

Lisa

 

[jee]

The resulting number from date arithemtic is a number of days - so to convert to seconds, simply multiply by the number of seconds in a day, 86400.

 

[JediDan]

I'm not sure what an interval is (in terms of Oracle datatypes), but assuming that both END_TIME and START_TIME are dates, then you could use the following:

to_number(end_time - start_time)*86400

(86400 = 24 * 60 * 60)

This will give you the difference between start time and stop time in seconds.

Good luck.

 

[lyanch]

You can't convert an interval to a number.. the error you get with to)number is:

ORA-01722: invalid number

Is is specifically excluded from the conversions available..

I only see how to convert it to a char (or the variations thereof) which doesn't help a lot because the format is always something like:

+000000000 00:03:03.875000

I would even do the math myself if I could figure out how to extract hours, minutes seconds.. If I have to break down the char to get the numbers I will.. it just seems to me that this isn't a rocket science here... lots of people probably do this.. there has to be a function that I can't find.

Lisa

 

[lyanch]

Okay I found it.. I said extract and there it was...

Extract (SECOND from (MAX_AVAILABLE_END_TIME - START_TIME))

Extract (Minute from (MAX_AVAILABLE_END_TIME - START_TIME))

etc

Thanks

Lisa

 

[Turkbear]

Hi,
Ah, those new 9i functions....

 

[JediDan]

Note that you CAN do a to_number() where the items inside the to_number call are dates... I do it all the time. But if you have fancy 9i functions, then by all means use them! (Or write your own timestamp formatting function).

 

[lyanch]

You won't be able to if you go to 9i (to_number dates). The exact formula you provided fails in 9i. It was one of the first I tried. Date arithmetic now returns an interval datatype, which cannot be cast to a number.

Lisa

 

[JediDan]

Interesting. We're still on 8.1.7 for the time being... In that case, can a column be declared as having an interval data type, or it only the result of a calculation?