oracle time handlling [message #20988] |
Thu, 04 July 2002 21:44 |
js
Messages: 8 Registered: July 2002
|
Junior Member |
|
|
I am running Oracle8i. I need to find the elapsed time between times T1 and T2. The time should be resolved to month-day-year, hour-min-sec, in a human-readable form.
What is the simplest way to deal with date/time in Oracle8i? I don't believe that it supports the datetime type, and the use of Timestamps looks like complex overkill.
I keep futzing with to_char, trunc, and mod, but this must be a naive approach.
For example, the following statemtn returns time differences in oracle-speak. With this approach, I have to recalculate everything based on the representation of one minute: .00069444... pretty cumbersome, and inaccurate, too.
SQL> SELECT duration, mod((indate - outdate),2) FROM TEMP;
RESULT LISTING -->
DURATION MOD((INDATE-OUTDATE),2)
------------------------ -----------------------
1 sec .00001157
30 sec .00034722
1 min .00069444
5 min .00347222
10 min .00694444
30 min .02083333
35 min .02430556
59 min .04097222
1 hr .04166667
1.5 hr .0625
2 hr .08333333
6 hr .25
12 hr .5
1 day 1
1 day 1 hr 1.0416667
1 day 0 hr 1 min 1.0006944
1 day 12 hr 1.5
1 day 12 hr 1 min 1.5006944
1 day 12 hr, 30 min 1.5208333
2 day 12 hr, 35 min .52430556
QUESTION
Is there an accurate & simple Oracle8i function that will easilly calculate and display the difference between T1 and T2 in human-readable format?
|
|
|
|