OraFAQ Forum: SQL & PL/SQL » Duration between two times

Home » SQL & PL/SQL » SQL & PL/SQL » Duration between two times

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Duration between two times [message #217130]

Wed, 31 January 2007 15:29

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

Report message to a moderator

Re: Duration between two times [message #217131 is a reply to message #217130]

Wed, 31 January 2007 15:36

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Duration between two times [message #217133 is a reply to message #217131]

Wed, 31 January 2007 15:46

hubstack
Messages: 12
Registered: August 2005

Junior Member

Yes, but how can I convert the interval into a number datatype?

Report message to a moderator

Re: Duration between two times [message #217134 is a reply to message #217130]

Wed, 31 January 2007 15:51

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Duration between two times [message #217144 is a reply to message #217130]

Wed, 31 January 2007 17:26

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

Report message to a moderator

Re: Duration between two times [message #217145 is a reply to message #217130]

Wed, 31 January 2007 17:33

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Duration between two times [message #217146 is a reply to message #217145]

Wed, 31 January 2007 18:01

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

Report message to a moderator

Re: Duration between two times [message #217147 is a reply to message #217130]

Wed, 31 January 2007 18:20

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Duration between two times [message #217149 is a reply to message #217147]

Wed, 31 January 2007 18:43

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>

Report message to a moderator

Re: Duration between two times [message #217153 is a reply to message #217130]

Wed, 31 January 2007 19:08

BlackSwan
Messages: 26766
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

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.

Report message to a moderator

Re: Duration between two times [message #217160 is a reply to message #217130]

Wed, 31 January 2007 21:16

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Duration between two times [message #217237 is a reply to message #217160]

Thu, 01 February 2007 05:03

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.

Report message to a moderator

Re: Duration between two times [message #217299 is a reply to message #217237]

Thu, 01 February 2007 10:16

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;

Report message to a moderator

Re: Duration between two times [message #217304 is a reply to message #217130]

Thu, 01 February 2007 10:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>This one worked best:
I am curious.
"Best" based upon which metric & how is/was that measured?

Report message to a moderator

Re: Duration between two times [message #217307 is a reply to message #217130]

Thu, 01 February 2007 10:47

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

Report message to a moderator

Re: Duration between two times [message #217316 is a reply to message #217130]

Thu, 01 February 2007 12:01

BlackSwan
Messages: 26766
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)!

Report message to a moderator

Previous Topic:	insert record and increment date
Next Topic:	counting occurances

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:10:14 CDT 2024