"Dave Harris" <daharris_at_erinet.com> wrote in message news:<3deeb17e$0$17653$4c5ecdc7_at_news.erinet.com>...
> Thanks! I will try this and see what happens.
>
> Dave Harris
>
> "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> news:r4houu0705qgg6ggog0n3n7e6ggnpb1g29_at_4ax.com...
> > On Mon, 2 Dec 2002 23:45:41 -0500, "Dave Harris" <daharris_at_erinet.com>
> > wrote:
> >
> > > I just completed my first class in Oracle and for my final project I
> built
> > >a music CD database. I have a table called "songs" which has the song
> title
> > >and the running time (among other fields) and a table called "album"
> which
> > >links to the "songs" table. I wanted to do a report or a query which
> would
> > >add up all of the running times (which are declared as DATE datatype) for
> > >all of the albums in the database and give me a total running time for
> the
> > >each album.
> > > The teacher suggested I go back and replace the DATE running times with
> > >numeric values as he believes you can't manipulate the DATE datatypes. I
> was
> > >wondering if you could somehow convert the running time to a string then
> use
> > >a substring function to pull out the minutes and seconds values to use in
> > >the calculation then somehow put the final calculation back into a time
> > >format.
> > > I guess this is a long winded way of asking if the string conversion
> can
> > >work. Even though the class is over I still want to incorporate this in
> my
> > >database program. Any ideas and advice would be greatly appreciated.
> Thanks
> > >in advance!!!!
> > >
> > >Dave Harris
> > >daharris_at_erinet.com
> >
> >
> > I would
> > - use the same dummy date for all running times (the hours, minutes
> > and seconds of course are real)
> > - use 60 * to_number(to_char(<running time>,'mi')) +
> > to_number(to_char(<running time>,'ss')) to get the duration in seconds
> > - that should do it.
> >
Another trick:
if D_date is a dummy date, then
(<running time> - D_date) * 86400 gives you duration in seconds
(<running time> - D_date) * 1440 gives you duration in minutes (with
fractures). You can actually do sum((<running time> - D_date) * 86400)
to sum all durations in seconds and calculate mintes and hours later.
> > Hth
> >
> >
> > >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
Received on Thu Dec 05 2002 - 11:12:45 CST
