Hello all. I browsed through the Oracle groups looking for an answer
to this and I found it asked before but in different circumstances.

I am trying to use a column to store just a time with no date. The
other posters that I saw were told to just make sure the date is the
same for all columns and use a normal date column. However, it
appeared to me that most people that asked the question were looking
for an absolute time-- i.e. 12:17AM and they just don't care about the
date. I am looking to store an elapsed time, like 47 minutes and 20
seconds.

I am trying to store race results and am therefore looking for a
column type to store a value that I can easily do computations on. For
example, "What is the mean time for all runners that are male and
between the ages of 17 and 24?" or "select all runners where finish
time is between 45 minutes and 50 minutes".

The times I am loading into the table are ascii and in 'hh:mi:ss'
format.

I would appreciate some recommendations based on the queries I
mentioned. I can easily write a program to convert the times to
seconds and use an integer column, but I don't know if that is a good
way to go. Any suggestions?

Thanks.

Bill,

If I were you I'd store results in seconds. For example:

NUMBER(9,3) can store any number of seconds up to about 10 days to
1/1000th of a second.

You can write PL/SQL functons to do such things as:

* convert HH:MM:SS to seconds
* convert seconds to a 'hh:mm:ss' string.

You could then use these functions in SQL statements for reporting etc.

An example:

create table racetimes(race_runner varchar2(30)
,race_time number(9,3))
/



create or replace function hhmsss_to_seconds (p_hhmmss varchar2) return
number as
--
l_hours number(2) := null;
l_mins number(2) := null;
l_secs number(2) := null;
l_timeinsecs number(9,3);
--
BEGIN
--
l_hours := substr(p_hhmmss,1,2);
l_mins := substr(p_hhmmss,4,2);
l_secs := substr(p_hhmmss,7,2);
--
l_timeinsecs := l_secs
+ (l_mins * 60)
+ (l_hours * 3600);
--
return(l_timeinsecs);
--
END;
/

create or replace function seconds_to_hhmmss (p_seconds number) return
varchar2 as
--
l_date date;
l_string varchar2(30);
--
BEGIN
--
-- Cheat by making up a date, adding our time to it and then printing
-- out the
-- hhmmss part. This works because Oracle treats dates like numbers,
-- with the decimal
-- part being fraction of a day.
--
l_date := trunc(sysdate); -- Trunc sysdate is today, midnight
l_date := l_date + (p_seconds / (60 * 60 * 24)); -- turn our seconds

-- into decimal part of a day
--
return(to_char(l_date, 'HH24:MI:SS'));
--
END;
/


1* select hhmsss_to_seconds('01:00:01') from dual

HHMSSS_TO_SECONDS('01:00:01')
-----------------------------
3601

SQL> select seconds_to_hhmmss(&a) from dual;
Enter value for a: 1
old 1: select seconds_to_hhmmss(&a) from dual
new 1: select seconds_to_hhmmss(1) from dual

SECONDS_TO_HHMMSS(1)
--------------------------------------------------------------------------------
00:00:01

SQL> r
1* select seconds_to_hhmmss(&a) from dual
Enter value for a: 3601
old 1: select seconds_to_hhmmss(&a) from dual
new 1: select seconds_to_hhmmss(3601) from dual

SECONDS_TO_HHMMSS(3601)
--------------------------------------------------------------------------------
01:00:01

SQL> r
1* select seconds_to_hhmmss(&a) from dual
Enter value for a: 60
old 1: select seconds_to_hhmmss(&a) from dual
new 1: select seconds_to_hhmmss(60) from dual

SECONDS_TO_HHMMSS(60)
--------------------------------------------------------------------------------
00:01:00

SQL> r
1* insert into racetimes values ('J Hoffa',
hhmsss_to_seconds('02:00:00'))

1 row created.

SQL> select * from racetimes;

RACE_RUNNER RACE_TIME
------------------------------ ----------
J Hoffa 7200

SQL> select race_runner, race_time, seconds_to_hhmmss(race_time) from
racetimes;

RACE_RUNNER RACE_TIME
------------------------------ ----------
SECONDS_TO_HHMMSS(RACE_TIME)
--------------------------------------------------------------------------------
J Hoffa 7200
02:00:00




David Rolfe
Orinda Software
Dublin, Ireland
-----------------------------------------------------------------------
Makers of OrindaBuild Which Writes Java To Run PL/SQL Functions
www.orindasoft.com


> Hello all. I browsed through the Oracle groups looking for an answer
> to this and I found it asked before but in different circumstances.
>
> I am trying to use a column to store just a time with no date. The
> other posters that I saw were told to just make sure the date is the
> same for all columns and use a normal date column. However, it
> appeared to me that most people that asked the question were looking
> for an absolute time-- i.e. 12:17AM and they just don't care about the
> date. I am looking to store an elapsed time, like 47 minutes and 20
> seconds.
>
> I am trying to store race results and am therefore looking for a
> column type to store a value that I can easily do computations on. For
> example, "What is the mean time for all runners that are male and
> between the ages of 17 and 24?" or "select all runners where finish
> time is between 45 minutes and 50 minutes".
>
> The times I am loading into the table are ascii and in 'hh:mi:ss'
> format.
>
> I would appreciate some recommendations based on the queries I
> mentioned. I can easily write a program to convert the times to
> seconds and use an integer column, but I don't know if that is a good
> way to go. Any suggestions?
>
> Thanks.

Oracle 9i and up implement the INTERVAL DAY TO SECOND SQL datatype.

"Bill McLaughlin" <mcbill20@hotmail.com> wrote in message
news:e9cbc4f2.0406020210.674fd90c@posting.google.c om...
> Hello all. I browsed through the Oracle groups looking for an answer
> to this and I found it asked before but in different circumstances.
>
> I am trying to use a column to store just a time with no date. The
> other posters that I saw were told to just make sure the date is the
> same for all columns and use a normal date column. However, it
> appeared to me that most people that asked the question were looking
> for an absolute time-- i.e. 12:17AM and they just don't care about the
> date. I am looking to store an elapsed time, like 47 minutes and 20
> seconds.
>
> I am trying to store race results and am therefore looking for a
> column type to store a value that I can easily do computations on. For
> example, "What is the mean time for all runners that are male and
> between the ages of 17 and 24?" or "select all runners where finish
> time is between 45 minutes and 50 minutes".
>
> The times I am loading into the table are ascii and in 'hh:mi:ss'
> format.
>
> I would appreciate some recommendations based on the queries I
> mentioned. I can easily write a program to convert the times to
> seconds and use an integer column, but I don't know if that is a good
> way to go. Any suggestions?
>
> Thanks.

Thanks to both of you for the info. I ended up using the INTERVAL DAY
TO SECOND to store the data. I hope I made the right choice. Now my
main problem is using this column in calculations. It's easy to select
based on interval ranges, but I am having difficult using other
functions (like mean and average) as they are expecting numeric
values. I am going through the SQL reference to find the solution.
However, if anyone can point me to any specific documentation or
examples I'd appreciate it.

Thanks in advance.

Bill

"David Best" <davebest@usa_dot_net> wrote in message news:<X-ydnY_7luJ_jSPdRVn-gQ@speakeasy.net>...
> Oracle 9i and up implement the INTERVAL DAY TO SECOND SQL datatype.
>
> "Bill McLaughlin" <mcbill20@hotmail.com> wrote in message
> news:e9cbc4f2.0406020210.674fd90c@posting.google.c om...
> > Hello all. I browsed through the Oracle groups looking for an answer
> > to this and I found it asked before but in different circumstances.
> >
> > I am trying to use a column to store just a time with no date. The
> > other posters that I saw were told to just make sure the date is the
> > same for all columns and use a normal date column. However, it
> > appeared to me that most people that asked the question were looking
> > for an absolute time-- i.e. 12:17AM and they just don't care about the
> > date. I am looking to store an elapsed time, like 47 minutes and 20
> > seconds.
> >
> > I am trying to store race results and am therefore looking for a
> > column type to store a value that I can easily do computations on. For
> > example, "What is the mean time for all runners that are male and
> > between the ages of 17 and 24?" or "select all runners where finish
> > time is between 45 minutes and 50 minutes".
> >
> > The times I am loading into the table are ascii and in 'hh:mi:ss'
> > format.
> >
> > I would appreciate some recommendations based on the queries I
> > mentioned. I can easily write a program to convert the times to
> > seconds and use an integer column, but I don't know if that is a good
> > way to go. Any suggestions?
> >
> > Thanks.

Bill McLaughlin wrote:
> Thanks to both of you for the info. I ended up using the INTERVAL DAY
> TO SECOND to store the data. I hope I made the right choice. Now my
> main problem is using this column in calculations. It's easy to select
> based on interval ranges, but I am having difficult using other
> functions (like mean and average) as they are expecting numeric
> values. I am going through the SQL reference to find the solution.
> However, if anyone can point me to any specific documentation or
> examples I'd appreciate it.

Now you know why I didn't recommend INTERVAL DAY TO SECOND.

Also: Are you planning on using JDBC to access this table? The JDBC
drivers don't support the INTERVAL data types very well.

David Rolfe
Orinda Software
Dublin, Ireland
---------------------------------------------------------------------
Orinda Software makes OrindaBuild, which can write a java class to
run any SQL statement
www.orindasoft.com

"Bill McLaughlin" <mcbill20@hotmail.com> wrote in message
news:e9cbc4f2.0406032216.78d2dbfc@posting.google.c om...
> Thanks to both of you for the info. I ended up using the INTERVAL DAY
> TO SECOND to store the data. I hope I made the right choice. Now my
> main problem is using this column in calculations. It's easy to select
> based on interval ranges, but I am having difficult using other
> functions (like mean and average) as they are expecting numeric
> values. I am going through the SQL reference to find the solution.
> However, if anyone can point me to any specific documentation or
> examples I'd appreciate it.
>
> Thanks in advance.
>
> Bill

I would recommend more experimentation before you settle on an approach;
interval types have many limitations. Here's a function that can help your
calculations:

-- converts interval day to second to number of seconds
function dsintervaltonum(val dsinterval_unconstrained) return number is
begin
return extract(day from val) * secs_per_day + extract(hour from val) *
secs_per_hour + extract(minute from val) * secs_per_minute + extract(second
from val);
end;

Dave

"David Best" <davebest@usa_dot_net> wrote in message news:<Gc2dnfzu2upRK13d4p2dnA@speakeasy.net>...
>
> I would recommend more experimentation before you settle on an approach;
> interval types have many limitations. Here's a function that can help your
> calculations:
>
> -- converts interval day to second to number of seconds
> function dsintervaltonum(val dsinterval_unconstrained) return number is
> begin
> return extract(day from val) * secs_per_day + extract(hour from val) *
> secs_per_hour + extract(minute from val) * secs_per_minute + extract(second
> from val);
> end;
>
> Dave

Thanks again to both of you. I agree that I need to do some more
experimentation before deciding on a particular format. Right now I am
going to add a "seconds" column and see how much work it is to get the
queries and reports I want from that. To be honest, all of the numeric
formats available in Oracle confuse me. Most of the programming I have
done has been accounting and shop floor type stuff-- calculating times
between dates, dollar amounts, etc. rather than integer stuff. I
rarely had columns that would only contain whole numbers. So, just
about everything I've put together in the past would be a column that
is "number(w,f)".

That said, can you point me to a good document that explains the
internal formats and recommendations for when to use each type? Most
of what I have found so far just gives a brief overview of what _can_
be stored in each format, rather than what _should_ be stored in a
particular format.

Thanks.

"Bill McLaughlin" <mcbill20@hotmail.com> wrote in message
news:e9cbc4f2.0406071851.35ee93ec@posting.google.c om...
> Thanks again to both of you. I agree that I need to do some more
> experimentation before deciding on a particular format. Right now I am
> going to add a "seconds" column and see how much work it is to get the
> queries and reports I want from that. To be honest, all of the numeric
> formats available in Oracle confuse me. Most of the programming I have
> done has been accounting and shop floor type stuff-- calculating times
> between dates, dollar amounts, etc. rather than integer stuff. I
> rarely had columns that would only contain whole numbers. So, just
> about everything I've put together in the past would be a column that
> is "number(w,f)".
>
> That said, can you point me to a good document that explains the
> internal formats and recommendations for when to use each type? Most
> of what I have found so far just gives a brief overview of what _can_
> be stored in each format, rather than what _should_ be stored in a
> particular format.
>
> Thanks.

I am not aware of anything like this on-line (but haven't really looked)
beyond Oracle's own documentation, which is pretty exhaustive. Perhaps
others can jump in and provide links. I'm fairly new to Oracle programming
and am doing most of it directly in PL/SQL accessed from Borland's Delphi.
"Oracle PL/SQL Programming, 3rd Edition" by Feuerstein & Pribyl has proven
to be an invaluable reference, with lots of in-depth information.

Hope that helps,
Dave