Home » SQL & PL/SQL » SQL & PL/SQL » Duration between two times
Duration between two times [message #217130] Wed, 31 January 2007 15:29 Go to next message
hubstack
Messages: 12
Registered: August 2005
Junior Member
Hello,

I'm trying to find the duration between two timestamps in seconds.


I've tried

select start_time, end_time, round((end_time-start_time)*24*60*60,0) from hs_temp;

but get this error:

1:26:10 PM ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

What can I do?

Thanks
Re: Duration between two times [message #217131 is a reply to message #217130] Wed, 31 January 2007 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>1:26:10 PM ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

>What can I do?
To make the error go away change the INTERVAL to a NUMBER.
Only under certain circumstances will Oracle perform automagical data type conversion for you.
In cases like this manual data type conversion is required.
Re: Duration between two times [message #217133 is a reply to message #217131] Wed, 31 January 2007 15:46 Go to previous messageGo to next message
hubstack
Messages: 12
Registered: August 2005
Junior Member
Yes, but how can I convert the interval into a number datatype?
Re: Duration between two times [message #217134 is a reply to message #217130] Wed, 31 January 2007 15:51 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Are you incapable or unwilling to use Google?
Results 1 - 10 of about 95,700 for oracle convert the interval into a number datatype.
Re: Duration between two times [message #217144 is a reply to message #217130] Wed, 31 January 2007 17:26 Go to previous messageGo to next message
hubstack
Messages: 12
Registered: August 2005
Junior Member
This works:

select round((sysdate + gib_dur) - sysdate) sec_dur,
round(((sysdate + gib_dur) - sysdate)/60) min_dur,
round((((sysdate + gib_dur) - sysdate)/60)/60) hr_dur,
start_time, end_Time, gib_dur
from hs_Temp2 ;

where gib_dur is the Interval in seconds
Re: Duration between two times [message #217145 is a reply to message #217130] Wed, 31 January 2007 17:33 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
does
select start_time, end_time, round(to_number(end_time-start_time))*24*60*60,0) from hs_temp;
give the same results?
Re: Duration between two times [message #217146 is a reply to message #217145] Wed, 31 January 2007 18:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Unfortunately, TO_NUMBER doesn't handle INTERVAL types.

SQL> select start_time, end_time,
  2     round( extract( day from (end_time-start_time) )*24*60*60 +
  3          extract( hour from (end_time-start_time) )*60*60 +
  4          extract( minute from (end_time-start_time) )*60 +
  5          extract( second from (end_time-start_time)) )  Seconds from hs_temp;

START_TIME                     END_TIME                          SECONDS
------------------------------ ------------------------------ ----------
24-JAN-07 04.46.04.000000 PM   27-JAN-07 04.46.04.000000 PM       259200
28-JAN-07 04.46.04.000000 PM   29-JAN-07 04.46.04.000000 PM        86400
Re: Duration between two times [message #217147 is a reply to message #217130] Wed, 31 January 2007 18:20 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member

1 select INC_EXTRACT_TIME,DATE_CREATED, round(to_number(INC_EXTRACT_TIME-DATE_CREATED)*24*60*60) duration_in_secs
2* from account_status where database_name = 'DW_2000000010662'
SQL> /

INC_EXTRACT_TIME DATE_CREATED DURATION_IN_SECS
------------------- ------------------- ----------------
2006-12-04 00:00:00 2006-08-24 10:06:58 8776382

I suspect that Oracle does implicit conversion from INTERVAL to CHARACTER before applying TO_NUMBER, but it does does seem to work for me.
Re: Duration between two times [message #217149 is a reply to message #217147] Wed, 31 January 2007 18:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Works when the datatype is DATE, but doesn't seem to work with TIMESTAMP. OP mentioned seconds between two TIMESTAMPs. Hopefully this will be an 11g enhancement.

SQL> desc hs_temp2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 START_TIME                                         DATE
 END_TIME                                           DATE

SQL> select start_time, end_time, round(to_number(end_time-start_time)*24*60*60,0) Seconds from hs_temp2;

START_TIME                     END_TIME                          SECONDS
------------------------------ ------------------------------ ----------
24-JAN-07 19:28:34             27-JAN-07 19:28:34                 259200
28-JAN-07 19:28:34             29-JAN-07 19:28:34                  86400

SQL> desc hs_temp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 START_TIME                                         TIMESTAMP(6)
 END_TIME                                           TIMESTAMP(6)

SQL> select start_time, end_time, round(to_number(end_time-start_time)*24*60*60,0) Seconds from hs_temp;
select start_time, end_time, round(to_number(end_time-start_time)*24*60*60,0) Seconds from hs_temp
                                                     *
ERROR at line 1:
ORA-01722: invalid number


SQL>
Re: Duration between two times [message #217153 is a reply to message #217130] Wed, 31 January 2007 19:08 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member

  1* select  end_time, beg_time,  to_number(substr((((end_time-beg_time)*(24*60*60))),2,9)) interval from ts_test
SQL> /

END_TIME
---------------------------------------------------------------------------
BEG_TIME                                                                      INTERVAL
--------------------------------------------------------------------------- ----------
15-JAN-07 01.01.01.000000 PM
15-JAN-07 12.01.00.000000 PM                                                      3601

[Updated on: Wed, 31 January 2007 19:34] by Moderator

Report message to a moderator

Re: Duration between two times [message #217159 is a reply to message #217153] Wed, 31 January 2007 21:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Nice alternative anacedent. May just need to modify it to accommodate for the sign if needed by the OP.
Re: Duration between two times [message #217160 is a reply to message #217130] Wed, 31 January 2007 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
The sign of INTERVAL is in position #1 of the SUBSTR,but I started at the 2nd character.
Initially I thought it looked ugly so chopped it out of consideration.
Re: Duration between two times [message #217237 is a reply to message #217160] Thu, 01 February 2007 05:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
But without the substr, we'd be back to:
SQL> select start_time, end_time, to_number((end_time-start_time)*24*60*60) Seconds from hs_temp;
select start_time, end_time, to_number((end_time-start_time)*24*60*60) Seconds from hs_temp
                                                     *
ERROR at line 1:
ORA-01722: invalid number

In the OP's case, I wouldn't think they could have an END_TIME prior to a START_TIME, so the sign of the interval should be a moot point.
Re: Duration between two times [message #217299 is a reply to message #217237] Thu, 01 February 2007 10:16 Go to previous messageGo to next message
hubstack
Messages: 12
Registered: August 2005
Junior Member
This one worked best:

select start_time, end_time,
round( extract( day from (end_time-start_time) )*24*60*60 +
extract( hour from (end_time-start_time) )*60*60 +
extract( minute from (end_time-start_time) )*60 +
extract( second from (end_time-start_time)) ) Seconds from hs_temp;
Re: Duration between two times [message #217304 is a reply to message #217130] Thu, 01 February 2007 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>This one worked best:
I am curious.
"Best" based upon which metric & how is/was that measured?
Re: Duration between two times [message #217307 is a reply to message #217130] Thu, 01 February 2007 10:47 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
Sorry anacedent, but I think than your query is false.
In facte you are converting timestamp into date, and it could have some bad side into this way.
For exemple:

SQL>  select (systimestamp+1)-systimestamp diff from dual;

DIFF
-------------------
+000000000 23:59:59


so you havn't the good result and if you want for exemple to convert it in day you'll have:
SQL> select to_number(substr((((systimestamp+1)-systimestamp)*(24*3600)),1,10)/(3600*24)) from dual;

TO_NUMBER(SUBSTR((((SYSTIMESTA
------------------------------
             0.287673611111111




wich is totally false.

Another solution is to convert manually the timestamp into date but I don't know if it'll work with local time zone!

select (systimestamp+1) - (systimestamp+0) from dual;
(SYSTIMESTAMP+1)-(SYSTIMESTAMP
------------------------------
1
Re: Duration between two times [message #217316 is a reply to message #217130] Thu, 01 February 2007 12:01 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
aline
>Sorry anacedent, but I think than your query is false.
You are entitled to your opinion & free to use or not use what you deem appropriate.
With free advice, you get what you paid for it.
You're On Your Own (YOYO)!
Previous Topic: insert record and increment date
Next Topic: counting occurances
Goto Forum:
  


Current Time: Mon Dec 05 07:15:03 CST 2016

Total time taken to generate the page: 0.08685 seconds