Re: "Delta" or "elapsed" time column in Oracle

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Wed, 02 Jun 2004 12:00:44 +0100
Message-ID: <40BDB35C.6020001@orindasoft.com>

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.

Received on Wed Jun 02 2004 - 06:00:44 CDT