OraFAQ Forum: SQL & PL/SQL » SQL or PL/SQL query to sum and avg time stamps

Home » SQL & PL/SQL » SQL & PL/SQL » SQL or PL/SQL query to sum and avg time stamps (ORACLE 11g)

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

SQL or PL/SQL query to sum and avg time stamps [message #614751]

Mon, 26 May 2014 13:45

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Hello All,

my query writtens time stamps in the following format ,i need sql query to sum all these time stamps and avg of these time stamps.

Can someone help me on this.

Appreciate your help in advance.

+000000000 00:04:05.000000
+000000000 00:04:02.000000
+000000000 00:04:02.000000
+000000000 00:04:01.000000
+000000000 00:03:59.000000
+000000000 00:03:58.000000
+000000000 00:03:58.000000
+000000000 00:03:57.000000
+000000000 00:03:57.000000
+000000000 00:03:55.000000
+000000000 00:03:55.000000

Thanks,

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614753 is a reply to message #614751]

Mon, 26 May 2014 13:50

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

my query writtens time stamps ...

Where to? What is its data type?

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614754 is a reply to message #614751]

Mon, 26 May 2014 13:50

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Have a look at http://www.williamrobertson.pwp.blueyonder.co.uk/code/agg_dsinterval.typ.txt

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614755 is a reply to message #614754]

Mon, 26 May 2014 13:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>+000000000 00:04:05.000000
what datatype is above?

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614756 is a reply to message #614755]

Mon, 26 May 2014 14:10

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Thanks all for your responses. Sorry for not having full details.

Hope the following helps to understand.

i have a query which gets a time difference from two timestamps, which basically looks like this

SELECT (time_a - time_b) FROM t_tabel WHERE t_some_id IN('1','2','3');

then I get the output

0 0:4:0.0
1 0:15:0.0
0 0:20:0.0

DAYS HOURS24H:MINUTES:SECONDS:MILLISECONDS

Now I just want to sum up these times, I've tried several things but nothing wont work.

SELECT sum(time_a - time_b) FROM t_tabel WHERE t_some_id IN('1','2','3');

gives me: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SELECT sum(to_dsinterval(time_a - time_b)) FROM t_tabel
WHERE t_some_id IN('1','2','3');

gives me: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SELECT sum(SELECT time_a - time_b FROM t_tabel
WHERE t_some_id IN('1','2','3')) from dual;

gives me: missing expression

SELECT sum(SELECT to_dsinterval(time_a - time_b) FROM t_tabel
WHERE t_some_id IN('1','2','3')) from dual;

gives me: missing expression

Somehow it's got to work.

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614757 is a reply to message #614751]

Mon, 26 May 2014 14:21

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can create your own aggregate function like explained in User defined aggregates and intervals and AVG and SUM aggregate functions for INTERVAL DAY TO SECOND values

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614758 is a reply to message #614751]

Mon, 26 May 2014 14:25

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Timestamps or intervals? Based the sample you have day to second intervals, not timestamps. Same as adding dates, adding timestamps simply makes no sense. Anyway it's a pain aggregating intervals. For some reason Oracle allows adding intervals but not summing them. You can do something like:

with t as (
           select interval '0 00:04:05.000000'day to second duration from dual union all
           select interval '0 00:04:02.000000'day to second from dual union all
           select interval '0 00:04:02.000000'day to second from dual union all
           select interval '0 00:04:01.000000'day to second from dual union all
           select interval '0 00:03:59.000000'day to second from dual union all
           select interval '0 00:03:58.000000'day to second from dual union all
           select interval '0 00:03:58.000000'day to second from dual union all
           select interval '0 00:03:57.000000'day to second from dual union all
           select interval '0 00:03:57.000000'day to second from dual union all
           select interval '0 00:03:55.000000'day to second from dual union all
           select interval '0 00:03:55.000000'day to second from dual
          )
select  numtodsinterval(
                        sum(
                            extract(day from duration) * 86400 +
                            extract(hour from duration) * 3600 +
                            extract(minute from duration) * 60 +
                            extract(second from duration)
                           ),
                        'second'
                       ) total_duration,
        numtodsinterval(
                        sum(
                            extract(day from duration) * 86400 +
                            extract(hour from duration) * 3600 +
                            extract(minute from duration) * 60 +
                            extract(second from duration)
                           ) / count(duration),
                        'second'
                       ) avg_duration
  from  t
/

TOTAL_DURATION                                                              AVG_DURATION
--------------------------------------------------------------------------- -----------------------------
+000000000 00:43:49.000000000                                               +000000000 00:03:59.000000000

SQL>

SY.

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614759 is a reply to message #614756]

Mon, 26 May 2014 14:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Oracle enforces variable datatype.
Datatype conversion is done either by implicit or explicit conversion.
When you subtract two TIMESTAMPS, the resultant datatype is INTERVAL DAY TO SECOND.
If you desire to add these values, you must explicitly convert the INTERVAL DAY TO SECOND values to NUMBER before applying the SUM() function.

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614760 is a reply to message #614759]

Mon, 26 May 2014 14:28

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Thank you guys for all your inputs.

Report message to a moderator

Re: SQL or PL/SQL query to sum and avg time stamps [message #614761 is a reply to message #614757]

Mon, 26 May 2014 14:30

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can also use the following trick:

SQL> select * from t;
VAL
---------------------------------------------------------------------------
+000000000 00:04:05.000000
+000000000 00:04:02.000000
+000000000 00:04:02.000000
+000000000 00:04:01.000000
+000000000 00:03:59.000000
+000000000 00:03:58.000000
+000000000 00:03:58.000000
+000000000 00:03:57.000000
+000000000 00:03:57.000000
+000000000 00:03:55.000000
+000000000 00:03:55.000000

11 rows selected.

SQL> select numtodsinterval(sum(extract(day from val)), 'day') +
  2         + numtodsinterval(sum(extract(hour from val)), 'hour') +
  3         + numtodsinterval(sum(extract(minute from val)), 'minute') +
  4         + numtodsinterval(sum(extract(second from val)), 'second') res
  5  from t;
RES
---------------------------------------------------------------------------
+000000000 00:43:49.000000000

Report message to a moderator

Previous Topic:	how to send email when the status of the object gets invalid?
Next Topic:	ORA-8177 when execute procedure from forms10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 01 00:45:33 CDT 2025