6 Replies Latest reply: Feb 16, 2011 3:37 AM by fac586 RSS

    Field with time in minutes and seconds

    839994
      Dear community,

      first of all, I am using Apex 3.1 on Oracle 10g, if this is necessary to know (yes I know, an old version, but given by the company I'm working for). After I've developed a few applications in Apex, thank to the forum and all the answers inside, I came to a problem that I could not solve:

      I want to have a normal text field, where the user can enter a duration of an event (e.g. "3:45" or "2:30"). That duration will allways be in the format "MM:SS". No hours, no milliseconds... But how can I save the given values in the database, so in which format? A varchar would be a possibility, but I want to build a report later on, where there is a sum of the times. And how to get a sum of varchar?! But how to make it clear to Apex and Oracle DB that the users gives in a time and not a varchar? The only thing oracle offers is a date field, but I want to save a time and not a date!

      Does anybody has a clue for me, cause it seems to me, that possibly I just don't know what I'm searching for, cause people must have had this problem before!?

      THanks for your help!

      Regards
      hoge
        • 1. Re: Field with time in minutes and seconds
          scarfy
          Hi,
          consider storing time in database in seconds in NUMBER column, easy to sum/sub etc and to convert to and from MI:SS format:)
          • 2. Re: Field with time in minutes and seconds
            839994
            Hi,

            yes I thought about this possibility, but that would mean, I have to use a computation before saving the value to the DB. But how to write this computation? I have to multiply the minutes with 60 and add the seconds. But I have to cut the "3:20" in "3" and "20" somehow, and I actually have no clue on how to do this?

            And the second thing is, that in the report, that I have to build afterwards, the column "duration" has to be in the format "MM:SS" once again, and there has to be a sum on the duration. Possibly a computation is the solution once again. But I hoped that there must be a much smarter solution for this?! Any more ideas? Or any help on how to get this solution working?!
            • 3. Re: Field with time in minutes and seconds
              fac586
              I want to have a normal text field, where the user can enter a duration of an event (e.g. "3:45" or "2:30"). That duration will allways be in the format "MM:SS". No hours, no milliseconds... But how can I save the given values in the database, so in which format?
              The appropriate SQL type would be <tt>INTERVAL DAY TO SECOND</tt>. Unfortunately this is not supported in APEX in terms of report column formatting and summaries.

              (Pleas update your forum profile with a better handle than "user8662340".)
              • 4. Re: Field with time in minutes and seconds
                scarfy
                first of all i suggest u to create database functions:
                create or replace
                FUNCTION sec_to_time(p_sec IN NUMBER) RETURN VARCHAR2
                IS
                  v_hours NUMBER;
                  v_minues NUMBER;
                  v_seconds NUMBER;
                BEGIN
                  -- hours
                  v_hours := floor( p_sec/3600 );
                  
                  -- minutes
                  v_minues := floor( ( p_sec - ( v_hours * 3600 ) ) / 60 );
                  
                  --seconds
                  v_seconds := mod( p_sec, 60 );
                  
                  return lpad( v_hours, 2,'0' )||':'||lpad( v_minues, 2,'0' )||':'||lpad( v_seconds, 2,'0' );
                END;
                and
                create or replace
                FUNCTION time_to_sec(p_time IN VARCHAR2) RETURN NUMBER
                IS
                  v_hours NUMBER := 0;
                  v_minues NUMBER;
                  v_seconds NUMBER;
                BEGIN
                  --seconds
                  v_seconds := regexp_substr(p_time,'[[:digit:]]{2}',1,3);
                  
                  -- v_seconds is null that means thant format is MI:SS not HH:MI:SS
                  IF v_seconds IS NULL THEN
                    v_seconds := regexp_substr(p_time,'[[:digit:]]{2}',1,2);
                    
                    -- minutes
                    v_minues := regexp_substr(p_time,'[[:digit:]]{2}',1,1);
                  ELSE
                    -- minutes
                    v_minues := regexp_substr(p_time,'[[:digit:]]{2}',1,2);
                    
                    -- hours
                    v_hours := regexp_substr(p_time,'[[:digit:]]{2}',1,1);
                  END IF;
                  
                  return v_seconds + v_minues*60 + v_hours*3600;
                END;
                That's all!

                And remember to do validations to keep good format of your time items:)
                • 5. Re: Field with time in minutes and seconds
                  839994
                  Wow, thanks for your help! I'll try this tomorrow morning in the office and will give a reply if it works, or ask again ;-)
                  • 6. Re: Field with time in minutes and seconds
                    fac586
                    But how to make it clear to Apex and Oracle DB that the users gives in a time and not a varchar? The only thing oracle offers is a date field, but I want to save a time and not a date!
                    All APEX items are character strings, so implicit or explicit conversion is required when the values are stored in database columns. For the recommended <tt>INTERVAL DAY TO SECOND</tt> columns, use <tt>TO_DSINTERVAL</tt> to perform the conversion.

                    Use <tt>EXTRACT</tt> to get the minute and second values from the <tt>INTERVAL</tt> going in the other direction.
                    but I want to build a report later on, where there is a sum of the times.
                    A user-defined aggregate function can be created to sum <tt>INTERVAL DAY TO SECOND</tt> values: here's an example.

                    APEX's lack of built-in support for summaries on <tt>INTERVAL</tt> types can be worked round by including the sum in the report query using a </tt>UNION</tt>:
                    SQL&gt; with test_data as (
                      2      select
                      3                rownum n
                      4              , numtodsinterval(ceil(dbms_random.value(0, 3600)), 'SECOND') t
                      5      from
                      6                dual
                      7      connect
                      8                by rownum &lt;= 10)
                      9  select
                     10            n
                     11          , t
                     12          ,    to_char(extract(minute from t))
                     13            || ':'
                     14            || to_char(extract(second from t), 'fm00') t1
                     15  from
                     16            test_data
                     17  union all
                     18  select
                     19            null
                     20          , sum_dsinterval(t)
                     21          ,    to_char(extract(hour from sum_dsinterval(t)))
                     22            || ':'
                     23            || to_char(extract(minute from sum_dsinterval(t)), 'fm00')
                     24            || ':'
                     25            || to_char(extract(second from sum_dsinterval(t)), 'fm00')
                     26  from
                     27            test_data
                     28  order by
                     29            1 nulls last
                     30  /
                    
                             N T                              T1
                    ---------- ------------------------------ ----------
                             1 +000000000 00:04:46.000000000  4:46
                             2 +000000000 00:07:05.000000000  7:05
                             3 +000000000 00:31:11.000000000  31:11
                             4 +000000000 00:53:16.000000000  53:16
                             5 +000000000 00:19:02.000000000  19:02
                             6 +000000000 00:57:12.000000000  57:12
                             7 +000000000 00:43:29.000000000  43:29
                             8 +000000000 00:03:11.000000000  3:11
                             9 +000000000 00:16:40.000000000  16:40
                            10 +000000000 00:20:49.000000000  20:49
                               +000000000 04:16:41.000000000  4:16:41
                    A custom named column (row) report template with conditional templates can be used to layout and format such a report so that the summary row(s) are properly handled.