Re: How do i store "TIME DURATION" in Oracle (what datatype shou i use ???)

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

How about seconds ???

Easy enough to convert on the way in (secs = hrs * 3600 + mins * 60 etc)

and easy enough to convert on the way

hrs = trunc ( secs / 3600 )
mins = (secs - hrs*3600)/60

etc

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse