1 2 Previous Next 22 Replies Latest reply on Nov 30, 2010 7:21 PM by Frank Kulash

    How to Count time values in varchar2 field.

    nazlfc
      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
        • 1. Re: How to Count time values in varchar2 field.
          Frank Kulash
          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.
          • 2. Re: How to Count time values in varchar2 field.
            BluShadow
            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>
            • 3. Re: How to Count time values in varchar2 field.
              nazlfc
              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
              • 4. Re: How to Count time values in varchar2 field.
                Frank Kulash
                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.
                • 5. Re: How to Count time values in varchar2 field.
                  nazlfc
                  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
                  • 6. Re: How to Count time values in varchar2 field.
                    Frank Kulash
                    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?
                    • 7. Re: How to Count time values in varchar2 field.
                      nazlfc
                      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
                      • 8. Re: How to Count time values in varchar2 field.
                        nazlfc
                        BTW, we are using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit.
                        • 9. Re: How to Count time values in varchar2 field.
                          Frank Kulash
                          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, ':').
                          • 10. Re: How to Count time values in varchar2 field.
                            hm
                            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
                            • 11. Re: How to Count time values in varchar2 field.
                              nazlfc
                              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
                              • 12. Re: How to Count time values in varchar2 field.
                                nazlfc
                                Thanks HM for updating Franks post. You have also answered my last post as well.

                                Thanks

                                Naz
                                • 13. Re: How to Count time values in varchar2 field.
                                  nazlfc
                                  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
                                  • 14. Re: How to Count time values in varchar2 field.
                                    Frank Kulash
                                    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
                                    1 2 Previous Next