This discussion is archived
6 Replies Latest reply: Feb 16, 2011 1:37 AM by fac586 RSS

Field with time in minutes and seconds

839994 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points