Oracle function help

[bubu2006]

I have to numeric columns cl1, cl2 where I am storing times. Now I want to create column 3 which is going to store cl1-cl2. Is there any way I can convert this number to time and then do a difference?

I can also convert the time into one unit and then do the difference. I have tried that & that is working, but I want to if there is any function?

 

[SantaMufasa]

Bubu,

Can you please offer examples of the "times" you are storing? By wanting to subtract the two times, it sounds as though you are storing a "START TIME" and an "END TIME". Why are you not storing a "START DATE/TIME" and an "END DATE/TIME" that are already in DATE formats?

Regardless, you can convert "TIME-ONLY" values to DATE values, then do DATE arithmetic:

select to_date('15:45','hh24:mi')-to_date('09:45','hh24:mi') diff
from dual;

DIFF
----
 .25

Notice that the 6-hour difference between the two times is ".25", a quarter of a 24-hour day. So, the result of subtracting two DATE expressions yields a difference in days (or part of a day).

If you want the result in hours, then simply multiply the original result by 24. So, using the same, original times:

select (to_date('15:45','hh24:mi')-to_date('09:45','hh24:mi'))*24 dif
from dual
/

DIFF
----
   6

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bubu2006]

101541 &101451

 

[bubu2006]

I am using number as datatype for both times. I have already mentioned that.

 

[SantaMufasa]

Interesting numbers, Bubu <grin>...Now, what do "101541" and "101451" represent?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

I have already mentioned that.

[sarcasm]Dave, how dare you not fully comprehend the above, grammatically innaccurate post. You are wasting this obviously important person's time. Please do not ask for clarification on information that the OP has already supplied[/sarcasm]
@OP, here's an idea, if you want to store times, use a datatype designed to store times.

 

[Beilstwh]

Is there also a field that contains the date of the start and end time. If so, then do something like this

select to_date(to_char(start_date,'MMDDYYYY')||TO_CHAR(START_TIME,'999999'),'MMDDYYYYHH24MISS') - to_date(to_char(END_date,'MMDDYYYY')||TO_CHAR(END_TIME,'999999'),'MMDDYYYYHH24MISS')
FROM MY_TABLE;

Bill
Oracle DBA/Developer
New York State, USA

 

[Beilstwh]

SORRY, MADE A TYPO.

select to_date(to_char(END_date,'MMDDYYYY')||TO_CHAR(END_TIME,'999999'),'MMDDYYYYHH24MISS') - to_date(to_char(START_date,'MMDDYYYY')||TO_CHAR(START_TIME,'999999'),'MMDDYYYYHH24MISS')
FROM MY_TABLE;

Bill
Oracle DBA/Developer
New York State, USA

 

[sem]

I suppose that TIMESTAMP fits much better for such purpose:

select cast (to_date('010101'||end_time, 'ddmmrrhh24miss') as timestamp)-
 cast (to_date('010101'||start time, 'ddmmrrhh24miss') as timestamp) from dual

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Sem!

Welcome back to tek-tips.

Now Santa has some serous competition again. You've dropped off the leader board, so get on out there and win lots of stars.

I nearly fell off my chair when I saw that Dima was amongst us once more.

Great to see your name in the threads again.

Regards

T

Grinding away at things Oracular

 

[Beilstwh]

That will work fine if the times are in the same day, but will fail if the (for example) start time is at 10:42pm and the end time is at 03:15am.

Bill
Oracle DBA/Developer
New York State, USA

 

[sem]

Thank you, Tharg,

glad to see you all!

Regards, Dima

http://seminihin.narod.ru