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

On Wed, 30 Aug 2000 04:22:04 -0400, "Alexandros Kotsiras, NYC" <alexandros_k_at_prodigy.net> wrote:

>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.
>

Yet I would use the date datatype. For the date I would use a dummy date, e.g. your own birthday. This will allow you to order by to_char(duration,'HH24:MI:SS'). No concerns about unnecessary constraints also.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Aug 30 2000 - 03:35:41 CDT