Re: How do i store "TIME DURATION" (what datatype should i use?)

Alex,

I have seen most of the replies in this thread, but have yet another opinion. I believe that the most flexible option may be to use your original idea but add yet a 4th field being the the total time in seconds. This option I have found to allow for both ease in searchs and comparisons of the total time. In your case you may also utilize a trigger on the table that allows you to insert only the total time field and populates the hour,minute,second fields. I found this technique most useful in the breakdown of dates: month, day, year ...

Nevin Hahn
nhahn_at_evoke.com

spencer wrote:

> just to add my opinion here... "Total Recorded Time" should
> be defined as a single column, and hold a number of seconds.
> you may want to add a single check constraint to ensure that
> the value is between zero and some reasonable upper bound.
> if you are not going to allow fractional seconds, i recommend
> defining the column as NUMBER(38,0) (aka INTEGER)
>
> "Alexandros Kotsiras, NYC" <alexandros_k_at_prodigy.net> wrote in message news:39ACC42C.318B8EC6_at_prodigy.net...
> > Hello,
> > I am building a database of Video Tapes.
> > Each tape (a record of the TAPE table) should have an attribute that
> > describes the "Total Recorded Time" of the tape
> > for example 1 Hour 25 minutes and 35 seconds. (This is not the capacity
> > of the Tape which can be 180 minutes etc)
> > Also that is the format that the user will enter the recorded time of a
> > tape through the interface , for example 3
> > text boxes for Hours, Minutes , Seconds.
> > I am wondering what is the best way to store this information.
> >
> > I was thinking of using 3 NUMBER fields in the Tape table (3 Attributes
> > called HOURS , MINUTES, SECONDS)
> > Is that approach right ?
> > Then i can easily order by HOURS, MINUTES, SECONDS in my sql statement.
> > Also i can easily enforce CHECK constraints as "check MINUTES between 0
> > and 59)
> >
> > I think that the DATE datatype is not appropriate since there is no date
> > concept (total number of seconds from the epoch) and then the order by
> > clause on a DATE datatype will not make sense when i decide to order
> > the tapes according to the Total Recorder Time.
> >
> > I would like to verify that my approach is right or I would like to hear
> > a better one.
> >
> > Thanks,
> > Alex.
> >
> >
> >
Received on Fri Sep 01 2000 - 01:57:06 CDT