Just store the time as a number of seconds. Then you could have a view that
separates the seconds into hours, minutes, seconds columns. Then sorting on
elapsed time is easy. You can have 2 functions one that takes hours,
minutes, seconds and converts it into seconds and one that takes seconds and
converts it into a fixed string hh:mi:ss e.g. (01:45:32).
Jim
"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 Thu Aug 31 2000 - 08:45:21 CDT
