Forum Stats

  • 3,733,256 Users
  • 2,246,740 Discussions
  • 7,856,637 Comments

Discussions

How to Count time values in varchar2 field.

nazlfc
nazlfc Member Posts: 75
edited November 2010 in SQL & PL/SQL
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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    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.
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    edited November 2010
    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>
  • nazlfc
    nazlfc Member Posts: 75
    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 ORA-01722: 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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited November 2010
    Hi, Naz,
    nazlfc wrote:
    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 ORA-01722: 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?
    As I said before, the solution I posted "assumes that the hours and minutes are always exactly 2 digits."
    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.
  • nazlfc
    nazlfc Member Posts: 75
    Hi Frank,

    Thanks for your help again. Unfortunately the time data is system generated by the application (it's an e-Learning 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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited November 2010
    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?
  • nazlfc
    nazlfc Member Posts: 75
    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
  • nazlfc
    nazlfc Member Posts: 75
    BTW, we are using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited November 2010
    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, ':').
  • hm
    hm Member Posts: 1,175
    edited November 2010
    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 nls-settings:
    -- 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
  • nazlfc
    nazlfc Member Posts: 75
    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
  • nazlfc
    nazlfc Member Posts: 75
    Thanks HM for updating Franks post. You have also answered my last post as well.

    Thanks

    Naz
  • nazlfc
    nazlfc Member Posts: 75
    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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited November 2010
    Hi,

    All built-in 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.930000000

    Is this correct?
    I get something even uglier: '+000000129 18:39:29.930000000'.
    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 seconds
    Exactly! 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
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,386 Black Diamond
    edited November 2010
    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:
    ORA-00932: 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.
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    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>
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    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
  • Sven W.
    Sven W. Member Posts: 10,507 Gold Crown
    Nice one Solomon!
  • nazlfc
    nazlfc Member Posts: 75
    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
  • Sven W.
    Sven W. Member Posts: 10,507 Gold Crown
    nazlfc wrote:
    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"))
    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.

    example untested
    SELECT 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 untested
    SELECT 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 */
    ;
  • nazlfc
    nazlfc Member Posts: 75
    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.

    Thanks
    Frank 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, ':').
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    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.