Home » SQL & PL/SQL » SQL & PL/SQL » average of time in HH:MM:SS format
- average of time in HH:MM:SS format [message #461254] Thu, 17 June 2010 04:59 Go to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
hi

can any one tell me how to calculate the average of the time in th e HH:MM:SS format stored database table..
column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------

1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00
1:40:00
0:17:00
0:30:00
0:40:00
0:30:00
21:08:00

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461256 is a reply to message #461254] Thu, 17 June 2010 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Simplest way would be to convert all the times to seconds.
Add them.
Average them.
Convert back to hours, minutes, seconds.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461259 is a reply to message #461256] Thu, 17 June 2010 05:11 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
can u please tell me the sample sql query..how to convert it...

i tried with my code but getting error


my query
select AVG(to_number(substr(MY_COL,1,2) ) * 3600
+ to_number(substr(MY_COL,4,2) ) * 60
+ to_number(substr(MY_COL,7,2) ))
from MY_DATA

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461260 is a reply to message #461256] Thu, 17 June 2010 05:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd just average the frational parts of the dates.

Code: [Select all] [Show/ hide]
SELECT to_char(trunc(sysdate) + avg(date_col - trunc(date_col))
              ,'hh24:mi:ss')
FROM   table

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461264 is a reply to message #461260] Thu, 17 June 2010 05:20 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
i have declared my time data column as varchar2(10)..

does it works with varchar2 datatype

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461269 is a reply to message #461264] Thu, 17 June 2010 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
column is declared as timestamp(6).

Quote:
have declared my time data column as varchar2(10)..

which one is true?

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 17 June 2010 05:40]

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461273 is a reply to message #461264] Thu, 17 June 2010 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
i have declared my time data column as varchar2(10)..

Well, that was a very silly thing to do if you want to store dates in it.

You can use the same solution, but you'll have to wrap each reference to your column with a TO_DATE(...) to convert the column value to a date.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461278 is a reply to message #461260] Thu, 17 June 2010 05:29 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
ORA-01722: invalid number

DBMS Output

am getting the above error when i tried to execute the sample query like u

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461282 is a reply to message #461254] Thu, 17 June 2010 05:37 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
shobhakashyapgmailcom wrote on Thu, 17 June 2010 15:29
hi

can any one tell me how to calculate the average of the time in th e HH:MM:SS format stored database table..
column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as varchar2(10).

MY COL
------

1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00
1:40:00
0:17:00
0:30:00
0:40:00
0:30:00
21:08:00

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461283 is a reply to message #461282] Thu, 17 June 2010 05:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Can time value be more than 24 hours?

SY.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461284 is a reply to message #461278] Thu, 17 June 2010 05:39 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
shobhakashyapgmailcom wrote on Thu, 17 June 2010 12:29
ORA-01722: invalid number


Then the format mask in the to_char function doesn't match the content of the column.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461285 is a reply to message #461283] Thu, 17 June 2010 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 17 June 2010 12:23

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 17 June 2010 05:40]

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461286 is a reply to message #461259] Thu, 17 June 2010 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
shobhakashyapgmailcom wrote on Thu, 17 June 2010 12:11
can u please tell me the sample sql query..how to convert it...

i tried with my code but getting error


my query
select AVG(to_number(substr(MY_COL,1,2) ) * 3600
+ to_number(substr(MY_COL,4,2) ) * 60
+ to_number(substr(MY_COL,7,2) ))
from MY_DATA

Which error? I don't see any error in your post.
Use SQL*Plus and copy and paste your session.

Regards
Michel

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461289 is a reply to message #461254] Thu, 17 June 2010 05:44 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
CREATE TABLE "T_DATA"
( "COL1" VARCHAR2(6) NOT NULL ENABLE,
"COL2" VARCHAR2(20) NOT NULL ENABLE,
"COL3" VARCHAR2(32) NOT NULL ENABLE,
"COL4" VARCHAR2(20) NOT NULL ENABLE,
"COL5" VARCHAR2(20) NOT NULL ENABLE,
"COL6" VARCHAR2(3) NOT NULL ENABLE,
"COL7" CHAR(3) NOT NULL ENABLE
)

col5 contains time data


1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00
1:40:00
0:17:00
0:30:00
0:40:00
0:30:00
21:08:00

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461290 is a reply to message #461289] Thu, 17 June 2010 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
col5 contains time data

Mine, build from what your posted, contains nothing.

Regards
Michel

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461291 is a reply to message #461286] Thu, 17 June 2010 05:46 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
ora-01722 error

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461293 is a reply to message #461291] Thu, 17 June 2010 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried using to_date as JRowbottom told you?

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461294 is a reply to message #461291] Thu, 17 June 2010 05:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Can time be more than 24 hours?

SY.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461296 is a reply to message #461291] Thu, 17 June 2010 05:48 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
From that error it sounds like you're not converting the field properly.

Of course that's a guess, could use more information.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461300 is a reply to message #461296] Thu, 17 June 2010 05:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Roachcoach wrote on Thu, 17 June 2010 06:48
From that error it sounds like you're not converting the field properly.


Obviously:

> 0:47:00

and

> to_number(substr(MY_COL,1,2) )

do not work together. It should be adjusted with INSTR, o better using regular expressions.

SY.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461302 is a reply to message #461286] Thu, 17 June 2010 05:53 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
michel am really confused and am sorry making u also i think


i have a code ca u please help me to frame in single query which may help me

SELECT to_char(lpad(my_TIME,8,'0')) as a
FROM SRS_CC_DATA

select AVG(to_number(substr(my_TIME,1,2) ) * 3600
+ to_number(substr(my_TIME,4,2) ) * 60
+ to_number(substr(my_TIME,7,2) ))
from SRS_CC_DATA

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461304 is a reply to message #461300] Thu, 17 June 2010 05:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or, assuming minutes and seconds are always two digits and hours do not exceed 99:

Code: [Select all] [Show/ hide]
select AVG(to_number(substr(lpad(MY_COL,8,'0'),1,2) ) * 3600 
+ to_number(substr(lpad(MY_COL,8,'0'),4,2) ) * 60
+ to_number(substr(lpad(MY_COL,8,'0'),7,2) ))
from MY_DATA


SY.
P.S. It could be adjusted same way if hours can be N digit number.

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461364 is a reply to message #461304] Thu, 17 June 2010 09:39 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Would solve the problem this way:
Code: [Select all] [Show/ hide]
--build a datetime from the given string 
--   and format the date as seconds past midnight (0-86399)
WITH dattim AS
  (SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
   SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL)
SELECT tim, to_char(tim,'SSSSS') ctim FROM dattim;   

TIM                     CTIM
-----------------------------
01.06.2010 05:12:45	18765
01.06.2010 13:36:01	48961
  
--add and avg the seconds 
WITH dattim AS
  (SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
   SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL)
SELECT avg(to_number(to_char(tim,'SSSSS'))) atim FROM dattim;   

ATIM
--------
33863

--build a date again
WITH dattim AS
  (SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
   SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL)
SELECT to_date(avg(to_number(to_char(tim,'SSSSS'))),'SSSSS') avgtim FROM dattim;   

avgtim
------------------
01.06.2010 09:24:23

This only works for times from ONE day. You should heed the advices to store the date not as a string, but as a genuin date!

[Updated on: Thu, 17 June 2010 09:39]

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461365 is a reply to message #461364] Thu, 17 June 2010 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I'm very fond of date function but I don't see any advantage to go to them here when a simple arithmetic computation as syakobson showed works.
Code: [Select all] [Show/ hide]
SQL> WITH 
  2    dattim AS (
  3      SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
  4      SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL
  5    )
  6  select AVG(to_number(substr(lpad(tim,8,'0'),1,2) ) * 3600 
  7         + to_number(substr(lpad(tim,8,'0'),4,2) ) * 60
  8         + to_number(substr(lpad(tim,8,'0'),7,2) )) sec
  9  from dattim 
 10  /
       SEC
----------
      3980

Code: [Select all] [Show/ hide]
SQL> WITH 
  2    dattim AS (
  3      SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
  4      SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL
  5    ),
  6    seconds as (
  7      select AVG(to_number(substr(lpad(tim,8,'0'),1,2) ) * 3600 
  8             + to_number(substr(lpad(tim,8,'0'),4,2) ) * 60
  9             + to_number(substr(lpad(tim,8,'0'),7,2) )) sec
 10      from dattim 
 11    )
 12  select to_char(trunc(sec/3600),'fm999999990')||':'||
 13         to_char(trunc(mod(sec,3600)/60),'fm00')||':'||
 14         to_char(mod(sec,60),'fm00') res
 15  from seconds
 16  /
RES
-------
1:06:20


Regards
Michel

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461367 is a reply to message #461365] Thu, 17 June 2010 10:00 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
I hate arithmetics and for me the average from 05:12:45 and 13:36:01 should be smoething near (my) 09:24:23 and not (your) 1:06:20 Laughing

[Updated on: Thu, 17 June 2010 10:03]

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461369 is a reply to message #461367] Thu, 17 June 2010 10:08 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're missing some to_char's, I get wildly different results:
Code: [Select all] [Show/ hide]
SQL> WITH 
  2    dattim AS (
  3         SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
  4         SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL
  5       )
  6     select AVG(to_number(substr(lpad(tim,8,'0'),1,2) ) * 3600 
  7            + to_number(substr(lpad(tim,8,'0'),4,2) ) * 60
  8            + to_number(substr(lpad(tim,8,'0'),7,2) )) sec
  9    from dattim;

       SEC
----------
     72001

SQL> WITH 
  2     dattim AS (
  3        SELECT to_date('05:12:45','HH24:MI:SS') tim FROM DUAL UNION ALL
  4        SELECT to_date('13:36:01','HH24:MI:SS')     FROM DUAL
  5      ),
  6      seconds as (
  7        select AVG(to_number(substr(lpad(tim,8,'0'),1,2) ) * 3600 
  8               + to_number(substr(lpad(tim,8,'0'),4,2) ) * 60
  9               + to_number(substr(lpad(tim,8,'0'),7,2) )) sec
 10        from dattim 
 11      )
 12    select to_char(trunc(sec/3600),'fm999999990')||':'||
 13           to_char(trunc(mod(sec,3600)/60),'fm00')||':'||
 14           to_char(mod(sec,60),'fm00') res
 15    from seconds;

RES
------------------
20:00:01

SQL> select sysdate from dual;

SYSDATE
---------------
20100617 154854

SQL>

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461370 is a reply to message #461367] Thu, 17 June 2010 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I was wrong to copy and paste your test case as it is not what OP has. Laughing

Code: [Select all] [Show/ hide]
SQL> WITH 
  2    dattim AS (
  3      SELECT '05:12:45' tim FROM DUAL UNION ALL
  4      SELECT '13:36:01'     FROM DUAL
  5    )
  6  select AVG(to_number(substr(lpad(tim,8,'0'),1,2) ) * 3600 
  7         + to_number(substr(lpad(tim,8,'0'),4,2) ) * 60
  8         + to_number(substr(lpad(tim,8,'0'),7,2) )) sec
  9  from dattim 
 10  /
       SEC
----------
     33863

1 row selected.

SQL> 
SQL> WITH 
  2    dattim AS (
  3      SELECT '05:12:45' tim FROM DUAL UNION ALL
  4      SELECT '13:36:01'     FROM DUAL
  5    ),
  6    seconds as (
  7      select AVG(to_number(substr(lpad(tim,8,'0'),1,2) ) * 3600 
  8             + to_number(substr(lpad(tim,8,'0'),4,2) ) * 60
  9             + to_number(substr(lpad(tim,8,'0'),7,2) )) sec
 10      from dattim 
 11    )
 12  select to_char(trunc(sec/3600),'fm999999990')||':'||
 13         to_char(trunc(mod(sec,3600)/60),'fm00')||':'||
 14         to_char(mod(sec,60),'fm00') res
 15  from seconds
 16  /
RES
------------------
9:24:23

1 row selected.

Regards
Michel

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461431 is a reply to message #461370] Thu, 17 June 2010 23:11 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member
the above code u mensioned was only which calculates 2 dates avg i think..but i have a data of more than 1000 rows and the column is declared as varchar2(10).

for your code to find the avg of two num am getting ora 01722 invalid number error
plzz help me to resolve

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461432 is a reply to message #461431] Thu, 17 June 2010 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>for your code to find the avg of two num am getting ora 01722 invalid number error

01722, 00000, "invalid number"
// *Cause: The specified number was invalid.
// *Action: Specify a valid number.

colon (":") is not a valid number ; I suspect a column alignment issue.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

- Re: average of time in HH:MM:SS format [message #461445 is a reply to message #461431] Fri, 18 June 2010 00:55 Go to previous message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
shobhakashyapgmailcom wrote on Fri, 18 June 2010 06:11
the above code u mensioned was only which calculates 2 dates avg i think..but i have a data of more than 1000 rows and the column is declared as varchar2(10).

for your code to find the avg of two num am getting ora 01722 invalid number error
plzz help me to resolve


You refuse to post any test case and follow the guide so:
- Fix your data.
- Modify the query to fit your need.
- Make a little effort to do your job yourself.

The query works any number of rows.

Regards
Michel

Report message to a moderator

Previous Topic: FIFO Method Calculation (merged once more)
Next Topic: How to make a range
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 28 01:29:06 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.