Datatype to use when applicaton Allows time to be entered
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Datatype to use when applicaton Allows time to be entered

  1. #1
    Join Date
    Jul 2006
    Posts
    1

    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
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You should use DATE data type for easy computation purpose.

    Code:
    SQL> select * from t1 ;
    
     STARTTIME    ENDTIME
    ---------- ----------
            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.

    Tamil

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    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