-
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.
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|