DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006

    Datatype to use when applicaton Allows time to be entered

    I have an application screen that allows users to enter the hour and minutes a job should be completed by on a 7 day window. For example, JOB ACB must finish by 10:00 AM everyday.

    My question is, what datatype do I use in Oracle to store this information? I'm thinking I would use two columns, one to store hours and the other to store minutes. We could store the time in a military format (i.e., 1:00 PM would be stored as 13:00). I would think it would not be a good idea to use the DATE datatype because the date is not needed. Should the two columns be CHAR(2) or NUMBER(2). Any opinions on this one? Thanks.

  2. #2
    Join Date
    May 2000
    You should use DATE data type for easy computation purpose.

    SQL> select * from t1 ;
    ---------- ----------
            30         45
           130        945
          2130        450
    SQL> get leadtime
      1  select starttime, endtime, st, et, et2, ((et2 - st)*24*60)  diff_in_min
      2  from
      3  (select starttime, endtime, st, et,
      4         case when et < st
      5              then et+1
      6              else et
      7          end as et2
      8  from (
      9  select starttime, endtime,
     10    to_date(to_char(trunc(sysdate),'YYYYMMDD')||substr('00'||starttime,-4)||'00',
     11   'YYYYMMDDHH24MISS') as st,
     12    to_date(to_char(trunc(sysdate),'YYYYMMDD')||substr('00'||endtime,-4)||'00',
     13   'YYYYMMDDHH24MISS') as et
     14  from t1 )
     15* )
    SQL> /
     STARTTIME    ENDTIME ST        ET        ET2       DIFF_IN_MIN
    ---------- ---------- --------- --------- --------- -----------
            30         45 19-JUL-06 19-JUL-06 19-JUL-06          15
           130        945 19-JUL-06 19-JUL-06 19-JUL-06         495
          2130        450 19-JUL-06 19-JUL-06 20-JUL-06         440
    You see how hard to code to find elapsed time.


  3. #3
    Join Date
    Nov 2000
    I feel your pain my friend. I suspect you're trying to write a system that basically says "do this at X time, regardless of the date". In other words, you only want to store the time componet of a date.

    I've done this before by storing a decimal number that represents the portion of a day. For example, .5 for noon. Then your date calculations are somewhat straight forward when calculating dates/times because you can just use date arithmetic.
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.