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

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Wed, 30 Aug 2000 15:15:17 -0700
Message-ID: <8ok0u4$mc$1@spiney.sierra.com>

use DATE field types:

create table test
(START_DATETIME date,
END_DATETIME date);

insert into test values (to_date('30-AUG-2000 15:20:00','DD-MON-YYYY HH24:MI:SS'),

                                         to_date('30-AUG-2000

15:10:00','DD-MON-YYYY HH24:MI:SS')); Then use date arithmetic to compute elapsed time.

SQL> select (start_datetime - end_datetime) * 24 * 60 timediff_in_minutes;

TIMEDIFF_IN_MINUTES

---

                 10

Note: 24 hours * 60 minutes = 1440

"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 Wed Aug 30 2000 - 17:15:17 CDT