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

From: <oratune_at_aol.com>
Date: Thu, 31 Aug 2000 15:41:51 GMT
Message-ID: <8olubq$mt5$1@nnrp1.deja.com>

In article <2jtr5.873$wS1.60471_at_nntp2.onemain.com>, "Barbara Kennedy" <barbken_at_teleport.com> wrote: > 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.
> >
> >
> >
>
>

Here is a sample function to return hours, minutes and seconds from the total seconds:

create or replace function get_hms(ttlsecs in integer) return varchar2 as

'Parking spaces' for our derived values
--
hrs integer; -- Hours mins integer; -- Minutes sec integer; -- Seconds
--
String to return formatted time
--
ttl_tme varchar2(8); begin
--
Determine the seconds
--
Using modulus arithmetic we
derive the remaining seconds from
the total value supplied
--
This will divide the total by 60 and
return the remainder as an integer
--
The remainder is the seconds that are
less than 60
--
sec := mod(ttlsecs, 60);
--
Subtract the 'odd' seconds from the
total seconds
--
This gives seconds that will be evenly
divisible by 60
--
Divide this new figure by 3600, the
number of seconds in an hour
--
The division is truncated to return
only whole hours
--
hrs := trunc((ttlsecs - sec) / 3600) ;
--
Having the seconds and the hours the
minutes calculation is fairly straightforward
--
From the total seconds subtract the 'odd'
seconds
--
From that new figure subtract the hours
multiplied by 3600
--
Divide this figure by 60
It will divide evenly and provide an
integer value between 0 and 59
--
mins := (ttlsecs - (hrs*3600) - sec) / 60;
--
Format the calculated pieces into a colon-delimited string
--
Left pad the values to two places with a leading 0
--
This properly formats the time
--
Example:
--
7142 seconds is 1 hour, 59 minutes and 2 seconds
1 hour becomes '01'
2 seconds becomes '02' so the string becomes
--
01:59:02
--
instead of
--
1:59:2
--
ttl_tme := lpad(hrs, 2, '0')||':'||lpad(mins, 2, '0')||':'||lpad(sec, 2, '0');
--
Return the calculated and formatted elapsed time
--
return ttl_tme; end; /

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Aug 31 2000 - 10:41:51 CDT