 3,733,256 Users
 2,246,740 Discussions
 7,856,637 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
 380.9K All Categories
 2.1K Data
 203 Big Data Appliance
 1.9K Data Science
 446.1K Databases
 220.4K General Database Discussions
 3.7K Java and JavaScript in the Database
 22 Multilingual Engine
 506 MySQL Community Space
 459 NoSQL Database
 7.7K Oracle Database Express Edition (XE)
 2.8K ORDS, SODA & JSON in the Database
 437 SQLcl
 3.9K SQL Developer Data Modeler
 185.4K SQL & PL/SQL
 20.7K SQL Developer
 291.2K Development
 6 Developer Projects
 116 Programming Languages
 288K Development Tools
 96 DevOps
 3K QA/Testing
 645.2K Java
 16 Java Learning Subscription
 36.9K Database Connectivity
 148 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.7K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 12 Java Essentials
 138 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 195 Java User Groups
 22 JavaScript  Nashorn
 Programs
 177 LiveLabs
 33 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 165 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
How to Count time values in varchar2 field.
nazlfc
Member Posts: 75
Hi,
I have a database field which stores time values i.e. time taken values (in this case session times) in the following format:
00:07:06.63
00:05:19.51
00:09:39.52
00:03:53.8
The field is of a VARCHAR2 datatype. What I would like to do is to get a sum of these times as Total times using SQL. I have tried using to_number to convert these varchar2 times but no avail. Please can anyone provide with some examples or possible functions that I maybe able to use. Your help wil be greatly appreciated.
Thanks
Naz
I have a database field which stores time values i.e. time taken values (in this case session times) in the following format:
00:07:06.63
00:05:19.51
00:09:39.52
00:03:53.8
The field is of a VARCHAR2 datatype. What I would like to do is to get a sum of these times as Total times using SQL. I have tried using to_number to convert these varchar2 times but no avail. Please can anyone provide with some examples or possible functions that I maybe able to use. Your help wil be greatly appreciated.
Thanks
Naz
Answers

Hi,
Here's one way to convert the string to a NUMBER:SELECT SUM ( (TO_NUMBER (SUBSTR (time_string, 1, 2)) * 60 * 60) + (TO_NUMBER (SUBSTR (time_string, 4, 2)) * 60) + (TO_NUMBER (SUBSTR (time_string, 7))) ) AS total_seconds FROM table_x;
This assumes that the hours and minutes are always exactly 2 digits.
The seconds don't have to be formatted exactly. '00:03:53.8' is fine.
If you want to display the number of seconds as hours:minutes:seconds (e.g. '00:25:59.46') then use the NUMTODSINTERVAL function. 
Or this
SQL> ed Wrote file afiedt.buf 1 with t as (select '00:07:06.63' as t from dual union all 2 select '00:05:19.51' from dual union all 3 select '00:09:39.52' from dual union all 4 select '00:03:53.8' from dual) 5  6  END OF TEST DATA 7  8 select to_char(trunc(h/360000),'fm00')':' 9 to_char(trunc(mod(h,360000)/6000),'fm00')':' 10 to_char(trunc(mod(h,6000)/100),'fm00')'.' 11 trunc(mod(h,100)) as sum_time 12 from ( 13 select sum(to_number(substr(t,1,2))*360000+ 14 to_number(substr(t,4,2))*6000+ 15 to_number(substr(t,7,2))*100+ 16 to_number(substr(t,10,2))) as h 17 from t 18* ) SQL> / SUM_TIME  00:25:58.74 SQL>

Hi Frank,
Thanks for your suggestion. The problem I am having still is getting the conversion of these values in varchar2 to number. I have used your sql but am getting a ORA01722: invalid number.
The datatype of the field that stores these values is a VARCHAR2 and I'm sure that the to_number function should convert this OK but it isn't happening.
Is there any reasons why it should not?
Thanks
Naz 
Hi, Naz,nazlfc wrote:As I said before, the solution I posted "assumes that the hours and minutes are always exactly 2 digits."
Hi Frank,
Thanks for your suggestion. The problem I am having still is getting the conversion of these values in varchar2 to number. I have used your sql but am getting a ORA01722: invalid number.
The datatype of the field that stores these values is a VARCHAR2 and I'm sure that the to_number function should convert this OK but it isn't happening.
Is there any reasons why it should not?
Apparantly, some of the data is not in the correct format. This is one of the many reasons why storing this kind of data in a VARCHAR2 column is such a bad idea. It should be converted to, and stored as, a NUMBER, or perhaps an INTERVAL DAY TO SECOND. That way, if a data entry error does occur, you catch it right away, when it's easiest to correct, and if you don't correct it, it doesn't keep you from working with the good data.
You can do something like this to find the bad data:SELECT * FROM table_x WHERE RTRIM ( TRANSLATE ( time_string , '012345678' , '999999999' ) , '9' ) NOT IN ( '99:99:99.' , '99:99:9.' , '99:99:.' , '99:99:' ) ;
Once you find the bad data, correct it (if possible) or remove it. 
Hi Frank,
Thanks for your help again. Unfortunately the time data is system generated by the application (it's an eLearning application). The data structure (i.e. data type and in this case VARCHAR2) of the column that stores this data is proprietary (vendor designed) so we are unable to change the data type to a number. This is the reason we are having such difficulty converting these time values.
Thanks
Naz 
Hi,
Regardless of who is responsible, storing data like this in VARCHAR2 column is a terrible idea. It's too bad you have to live with this situation.
It could be that all the data does conform to some format, but not the format that I was expecting based on the 4 values you originally psted..
Run the query I posted earlier. Post some of the time_strings that it displays; perhaps there's a fairly simple format that accomodates all of them. If it's not obvious, say what each of the strings mean.
Watch out for spaces and tabs in the data. If the bad data found contains any whitespace, make it clear whn you post it.
What version of Oracle (e.g. 10.2.0.3.0) are you using? 
Hi Frank,
There were 4 records that were abnormal from my point of view and they were of the format 0000:00:00.11
Your query would work if it wern't for these records. I need to find out why they are entered this way, but these are the erroneous records and the go by the same format.
Thanks
Naz 
BTW, we are using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0  64bit.

Hi,
This will allow the hours and minutes to have any number of digits:SELECT SUM ( (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 1)) * 60 * 60)  Hours (60 minutes of 60 seconds each) + (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 2)) * 60)  Minutes (60 seconds each) + (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 3)))  Seconds ) AS total_seconds FROM table_x;
It won't be as efficient as what I first psoted, but that may not matter much to you.
When there is a problem with my original solution, is it always that the hours are 4 digits rather than 2?
If so, you cound use a CASE expression, based on INSTR (time_string, ':'). 
I changed Frank's code a bit, because there may be a problem with the decimal separator in the last part. There may be a nls dependency: e.g. in germany the ',' is the default decimal separator.
This code should be independent of nlssettings: data: with table_x as ( select '00:07:06.63' as time_string from dual union all select '00:05:19.51' from dual union all select '00:09:39.52' from dual union all select '00:03:53.8' from dual)  Query: SELECT SUM ( (TO_NUMBER (SUBSTR (time_string, 1, 2)) * 60 * 60) + (TO_NUMBER (SUBSTR (time_string, 4, 2)) * 60) + (TO_NUMBER (SUBSTR (time_string, 7),'99D9999999','nls_numeric_characters=''.,''')) ) AS total_seconds FROM table_x;
or if you want:with table_x as ( select '00:07:06.63' as time_string from dual union all select '00:05:19.51' from dual union all select '00:09:39.52' from dual union all select '00:03:53.8' from dual)  Query: SELECT numtodsinterval( SUM ( (TO_NUMBER (SUBSTR (time_string, 1, 2)) * 60 * 60) + (TO_NUMBER (SUBSTR (time_string, 4, 2)) * 60) + (TO_NUMBER (SUBSTR (time_string, 7),'99D9999999','nls_numeric_characters=''.,''')) ) ,'second') FROM table_x;
Edited by: hm on 24.11.2010 09:58 
Hi Frank,
Thanks for your help again. That works and gives me the total seconds (persumably for the entire table). I've never used NUMTODSINTERVAL function before. How would I apply it to your last example to get a total time in the format of 00:00:00 or 00:00:00.0.
Thanks
Naz 
Thanks HM for updating Franks post. You have also answered my last post as well.
Thanks
Naz 
Hi HM,
Using the numtodsinterval and your solution I get a value as follows:
129 18:39:29.930000000
Is this correct? I'm not too sure about the first part 129. What does this mean? Can I strip it out? Unless it means 129 days 18 hours 39 minutes and 29 seconds
Without the numtodsinterval function, the total seconds are as follows: 11212769.93 
Hi,
All builtin functions, including NUMTODSINTERVAL, are documented in the SQL Language manual:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions108.htm#sthref1754
One way you might use it is:SELECT NUMTODSINTERVAL ( SUM ( (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 1)) * 60 * 60)  Hours (60 minutes of 60 seconds each) + (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 2)) * 60)  Minutes (60 seconds each) + (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 3)))  Seconds ) , 'SECOND' ) AS total_time FROM table_x;
nazlfc wrote:129 18:39:29.930000000I get something even uglier: '+000000129 18:39:29.930000000'.
Is this correct?
Perhaps you're using LTRIM to remove the leading '+' and '0's. (Good idea!)I'm not too sure about the first part 129. What does this mean? Can I strip it out? Unless it means 129 days 18 hours 39 minutes and 29 secondsExactly! The part before the space is the number of days. The hours will never be more than 23.
Edited by: Frank Kulash on Nov 24, 2010 1:46 PM
Added NUMTODSINTERVAL example 
You can easily convert your time strings to DAY TO SECOND intervals:
SQL> with t as ( 2 select '00:07:06.63' as time_str from dual union all 3 select '00:05:19.51' from dual union all 4 select '00:09:39.52' from dual union all 5 select '00:03:53.8' from dual 6 ) 7 select to_dsinterval('0 '  time_str) i 8 from t 9 / I  +000000000 00:07:06.630000000 +000000000 00:05:19.510000000 +000000000 00:09:39.520000000 +000000000 00:03:53.800000000 SQL>
Problem is, for some unknown reason Oracle, while supporting interval arithmetic does not support interval aggregation:SQL> with t as ( 2 select '00:07:06.63' as time_str from dual union all 3 select '00:05:19.51' from dual union all 4 select '00:09:39.52' from dual union all 5 select '00:03:53.8' from dual 6 ) 7 select sum(to_dsinterval('0 '  time_str)) i 8 from t 9 / select sum(to_dsinterval('0 '  time_str)) i * ERROR at line 7: ORA00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND SQL>
What you could do is create UDAF (user defined aggregate function) to sum day to second intervals:create or replace type dsi_type as object( dsi interval day(9) to second(9), static function ODCIAggregateInitialize( sctx in out dsi_type ) return number, member function ODCIAggregateIterate( self in out dsi_type, value in interval day to second ) return number, member function ODCIAggregateTerminate( self in dsi_type, returnvalue out interval day to second, flags in number ) return number, member function ODCIAggregateMerge( self in out dsi_type, ctx2 in dsi_type ) return number ); / create or replace type body dsi_type is static function ODCIAggregateInitialize( sctx in out dsi_type ) return number is begin sctx := dsi_type(null) ; return ODCIConst.Success ; end; member function ODCIAggregateIterate( self in out dsi_type, value in interval day to second ) return number is begin self.dsi := nvl(self.dsi,interval '0' day) + value ; return ODCIConst.Success; end; member function ODCIAggregateTerminate( self in dsi_type, returnvalue out interval day to second, flags in number ) return number is begin returnValue := self.dsi; return ODCIConst.Success; end; member function ODCIAggregateMerge( self in out dsi_type , ctx2 in dsi_type ) return number is begin self.dsi := self.dsi + ctx2.dsi; return ODCIConst.Success; end; end; / create or replace function dsi_sum( input interval day to second ) return interval day to second deterministic parallel_enable aggregate using dsi_type; /
Now you can do something like:with t as ( select '00:07:06.63' as time_str from dual union all select '00:05:19.51' from dual union all select '00:09:39.52' from dual union all select '00:03:53.8' from dual ) select regexp_replace(dsi_sum(to_dsinterval('0 '  time_str)),'^\+0{0,8}(.+?)0{0,7}$','\1') i from t / I  0 00:25:59.46 SQL>
SY. 
Ooops, just noticed a glitch in my solution. It was taking the .8 seconds as .08 by default. Now fixed...
SQL> with t as (select '00:07:06.63' as t from dual union all 2 select '00:05:19.51' from dual union all 3 select '00:09:39.52' from dual union all 4 select '00:03:53.8' from dual) 5  6  END OF TEST DATA 7  8 select to_char(trunc(h/360000),'fm00')':' 9 to_char(trunc(mod(h,360000)/6000),'fm00')':' 10 to_char(trunc(mod(h,6000)/100),'fm00')'.' 11 trunc(mod(h,100)) as sum_time 12 from ( 13 select sum(to_number(substr(t,1,2))*360000+ 14 to_number(substr(t,4,2))*6000+ 15 to_number(substr(t,7,2))*100+ 16 to_number(rpad(substr(t,10,2),2,'0'))) as h 17 from t 18 ) 19 / SUM_TIME  00:25:59.46 SQL>

Please remember model clause sometimes :8}
with t(Val) as( select '00:07:06.63' from dual union all select '00:05:19.51' from dual union all select '00:09:39.52' from dual union all select '00:03:53.8' from dual) select * from t model return updated rows dimension by(RowNum as soeji) measures(to_dsinterval('0 '  Val) as Val) rules iterate(100) UNTIL (presentV(Val[ITERATION_NUMBER+3],1,0) = 0) (Val[1] = Val[1]+Val[ITERATION_NUMBER+2]); SOEJI VAL   1 +000000000 00:25:59.460000000

Nice one Solomon!

Hi Everyone,
Thanks for all your help. I have one further issue. Now that I am able to aggregate the total time within this varchar2 time field, my next issue is to aggregate the times for particular groups within a pivot query. I am currently pivoting on status i.e. complete, passed and failed and my selected groupings are country, region etc...
I am struggling to embed the total_time solutions into the pivot such that I am able to get total times for a given status for a given country against a pivoted status. An example of the pivot query I have is below:
SELECT *
FROM
(SELECT country,
status
FROM times_table,
user_table,
WHERE user_table.userid = times_table.userid
) pivot (COUNT(status) FOR status IN ('completed' AS "Total Completed", 'passed' AS "Total Passed", 'incomplete' AS "Total Incomplete", 'failed' AS "Total Failed"))
Any help will be greatly appreciated 
nazlfc wrote:If you have only 3 status to choose from then the traditional approach seams better to me. Just create one column for each status and do a little different count.
SELECT *
FROM
(SELECT country,
status
FROM times_table,
user_table,
WHERE user_table.userid = times_table.userid
) pivot (COUNT(status) FOR status IN ('completed' AS "Total Completed", 'passed' AS "Total Passed", 'incomplete' AS "Total Incomplete", 'failed' AS "Total Failed"))
example untestedSELECT u.country ,count(*) "Total All" ,COUNT(case when t.status='completed' then 1 else null end) "Total Completed" ,COUNT(case when t.status='passed' then 1 else null end) "Total Passed" ,COUNT(case when t.status='incomplete' then 1 else null end) "Total Incomplete" ,COUNT(case when t.status='failed' then 1 else null end) "Total Failed" FROM times_table t JOIN user_table u on u.userid = t.userid group by u.country;
A complete different solution would be to use the rollup clause. The resulting data would be identical, just the representation would be different.
example untestedSELECT u.country ,t.status ,count(*) cnt FROM times_table t JOIN user_table u on u.userid = t.userid group by rollup(u.country, t.status) /* differnt grouping options could also be very usefull, depending on the requirements */ ;

Hi Frank,
Re: your post below:
I have notice in our production environment there are many instances where the time (which is a counter time) has four 0's e.g 0000:00:04:45.00. You mentioned using a CASE expression with the instr function. Please could you provide an example of this adapting the example in your post below.
ThanksFrank Kulash wrote:
Hi,
This will allow the hours and minutes to have any number of digits:SELECT SUM ( (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 1)) * 60 * 60)  Hours (60 minutes of 60 seconds each) + (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 2)) * 60)  Minutes (60 seconds each) + (TO_NUMBER (REGEXP_SUBSTR (time_string, '[^:]+', 1, 3)))  Seconds ) AS total_seconds FROM table_x;
It won't be as efficient as what I first psoted, but that may not matter much to you.
When there is a problem with my original solution, is it always that the hours are 4 digits rather than 2?
If so, you cound use a CASE expression, based on INSTR (time_string, ':'). 
Hi,
Did you try the REGEXP_SUBSTR approach? Was it too slow?
If you really want to use something based on SUBSTR, you can modify the solution in my first message like this:SELECT SUM ( CASE INSTR (time_string, ':') WHEN 3 THEN (TO_NUMBER (SUBSTR (time_string, 1, 2)) * 60 * 60) + (TO_NUMBER (SUBSTR (time_string, 4, 2)) * 60) + (TO_NUMBER (SUBSTR (time_string, 7))) WHEN 5 THEN (TO_NUMBER (SUBSTR (time_string, 1, 4)) * 60 * 60) + (TO_NUMBER (SUBSTR (time_string, 6, 2)) * 60) + (TO_NUMBER (SUBSTR (time_string, 9))) END ) AS total_seconds FROM table_x;
This will only accept hours that are exactly 2 or 4 characters. As before, minutes must have exactly 2 characters.
This discussion has been closed.