Re: how to count elapsed time between sysdate and some saved timestamp?

From: <fitzjarrell_at_cox.net>
Date: Wed, 7 May 2008 13:45:04 -0700 (PDT)
Message-ID: <49b5915f-05c7-4927-a75a-ab2df66609a2@l42g2000hsc.googlegroups.com>


On May 7, 3:08 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 7, 3:52 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
> > On May 7, 12:51 pm, sybra..._at_hccnet.nl wrote:
>
> > > On Wed, 7 May 2008 18:45:04 +0200, "buu" <a..._at_a.com> wrote:
> > > >question is simple... I have an date field in an table and I would like to
> > > >measure time difference (minutes or sec.) between current time and that
> > > >field.
>
> > > >at wich way you propose?
>
> > > Assuming a non-paleolithic version of Oracle (I appreciate, you, as
> > > someone asking help, can't be bothered to post it, as you assume
> > > Oracle never changes), one would just subtract the two dates (the unit
> > > of a date is a day), so you get the difference expressed as a days
> > > fraction and feed that through the numtodsinterval function.
>
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
>
> > With many thanks to William Robertson for sharing this on his Oracle
> > WTF site.
>
> > <sarcasm>
> > Oh, but it's much more fun to make things complicated:
>
> > SQL> --
> > SQL> -- Let's build a package of convoluted
> > SQL> -- mathematical il-logic to return what
> > SQL> -- would normally be the result of a
> > SQL> -- simple subtraction of dates
> > SQL> --
> > SQL> --
> > SQL> -- You simply can't beat complexity
> > SQL> --
> > SQL> -- Many thanks to William Robertson
> > SQL> -- for bringing this exquisite example
> > SQL> -- to my attention and for providing
> > SQL> -- the table population code
> > SQL> --
> > SQL>
> > SQL> CREATE PACKAGE dates_pkg
> >   2  AS
> >   3      FUNCTION julian_date
> >   4          ( date_to_convert DATE )
> >   5          RETURN NUMBER;
> >   6
> >   7      FUNCTION minutes_since_midnight
> >   8          ( timevalue DATE )
> >   9          RETURN NUMBER;
> >  10
> >  11      FUNCTION minutes_elapsed
> >  12          ( lowdate DATE
> >  13          , highdate DATE )
> >  14          RETURN NUMBER;
> >  15
> >  16  END dates_pkg;
> >  17  /
>
> > Package created.
>
> > Elapsed: 00:00:00.01
> > SQL>
> > SQL> CREATE PACKAGE BODY dates_pkg
> >   2  AS
> >   3      FUNCTION julian_date
> >   4          ( date_to_convert DATE)
> >   5          RETURN NUMBER
> >   6      IS
> >   7          varch_value VARCHAR (10);
> >   8          num_value NUMBER (20);
> >   9      BEGIN
> >  10          --
> >  11          -- First, we take a date and convert it to a date by
> > converting it
> >  12          -- to a character string using the same format we will use
> > to
> >  13          -- convert it BACK to a date again
> >  14          --
> >  15          -- Oh, then we convert it back to a character string
> >  16          --
> >  17          -- In Julian format, which is a number
> >  18          --
> >  19          SELECT TO_CHAR
> >  20                 ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/
> > YYYY')
> >  21                 , 'J')
> >  22          INTO   varch_value
> >  23          FROM   dual;
> >  24
> >  25          --
> >  26          -- Okay, so we had a Julian date as a number but we changed
> > it to
> >  27          -- a character string so we could go back and make it a ...
> >  28          -- NUMBER ... again
> >  29          --
> >  30          SELECT TO_NUMBER (varch_value)
> >  31          INTO   num_value
> >  32          FROM   dual;
> >  33
> >  34          --
> >  35          -- So, we finally make up our mind and keep it a number and
> >  36          -- return it from the function
> >  37          --
> >  38          RETURN (num_value);
> >  39      END julian_date;
> >  40
> >  41
> >  42      FUNCTION minutes_since_midnight (
> >  43          timevalue DATE)
> >  44          RETURN NUMBER
> >  45      IS
> >  46          secs_elapsed NUMBER (20);
> >  47          mins_elapsed NUMBER (20);
> >  48      BEGIN
> >  49          --
> >  50          -- So now we take a date and extract the time portion of
> > it,
> >  51          -- convert that BACK to a date, then convert THAT to a
> > string
> >  52          -- of seconds and convert THAT to a number
> >  53          --
> >  54          -- Is it me, or are we essentially driving across town just
> > to
> >  55          -- go next door?
> >  56          --
> >  57          SELECT TO_NUMBER
> >  58                 ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
> >  59                 , 'SSSSS') )
> >  60          INTO   secs_elapsed
> >  61          FROM   dual;
> >  62
> >  63          --
> >  64          -- Oooo, now we divide that total number of seconds by ...
> >  65          -- wait for it ...
> >  66          -- any second now ...
> >  67          -- 60!  Who would have thought that 60 seconds equals
> >  68          -- one minute?
> >  69          --
> >  70          SELECT (secs_elapsed / 60)
> >  71          INTO   mins_elapsed
> >  72          FROM   dual;
> >  73
> >  74          --
> >  75          -- Before we rest on our laurels we return the minutes
> > since midnight
> >  76          --
> >  77          RETURN (mins_elapsed);
> >  78      END minutes_since_midnight;
> >  79
> >  80
> >  81      FUNCTION minutes_elapsed
> >  82          ( lowdate DATE
> >  83          , highdate DATE )
> >  84          RETURN NUMBER
> >  85      IS
> >  86          final_number NUMBER (20);
> >  87          low_julian NUMBER (20);
> >  88          high_julian NUMBER (20);
> >  89          num_days NUMBER (20);
> >  90          num_minutes NUMBER (20);
> >  91          temp_mins NUMBER (20);
> >  92          min_low NUMBER (20);
> >  93          min_high NUMBER (20);
> >  94      BEGIN
> >  95          --
> >  96          -- Now, why didn't we use this julian_date function in the
> >  97          -- last installment of Julian conversions?
> >  98          --
> >  99          -- Oh, yeah, because we just WROTE that wonderful function
> > 100          --
> > 101          -- So, okay, we take our date values and return the Julian
> > 102          -- representations of them using all of the mathematical
> > 103          -- aerobics from earlier
> > 104          --
> > 105          -- I guess this is so much easier than simply subtracting
> > 106          -- them
> > 107          --
> > 108          SELECT julian_date (lowdate)
> > 109          INTO   low_julian
> > 110          FROM   dual;
> > 111
> > 112          SELECT julian_date (highdate)
> > 113          INTO   high_julian
> > 114          FROM   dual;
> > 115
> > 116          --
> > 117          -- Woo-hoo! Higher math time!  Subtract the Julian dates
> > 118          -- and get the number of days
> > 119          --
> > 120          -- Isn't that what we'd get if we just subtracted the
> > 121          -- submitted dates as-is?
> > 122          --
> > 123          -- Of course it is
> > 124          --
> > 125          SELECT (high_julian - low_julian)
> > 126          INTO   num_days
> > 127          FROM   dual;
> > 128
> > 129          --
> > 130          -- Now we calculate the total minutes elapsed
> > 131          -- using our values generated by our extreme
> > 132          -- gyrations
> > 133          --
> > 134          -- I'm out of breath just thinking about all of this work
> > 135          --
> > 136          SELECT (num_days * 1440)
> > 137          INTO   num_minutes
> > 138          FROM   dual;
> > 139
> > 140          --
> > 141          -- And now we put those other mathematical moves
> > 142          -- to use
> > 143          --
> > 144          -- Tell me again why we think we're smarter than
> > 145          -- the average bear?
> > 146          --
> > 147          SELECT minutes_since_midnight (lowdate)
> > 148          INTO   min_low
> > 149          FROM   dual;
> > 150
> > 151          SELECT minutes_since_midnight (highdate)
> > 152          INTO   min_high
> > 153          FROM   dual;
> > 154
> > 155          --
> > 156          -- Now this is disgusting
> > 157          --
> > 158          -- Using a TEMP variable to aid in simple mathematical
> > 159          -- processing
> > 160          --
> > 161          SELECT (min_high - min_low)
> > 162          INTO   temp_mins
> > 163          FROM   dual;
> > 164
> > 165          --
> > 166          -- And this is better than:
> > 167          -- select (end_date - start_date)*1440 because?
> > 168          --
> > 169          SELECT (num_minutes + temp_mins)
> > 170          INTO   final_number
> > 171          FROM   dual;
> > 172
> > 173          RETURN (final_number);
> > 174
> > 175      END minutes_elapsed;
> > 176  END dates_pkg;
> > 177  /
>
> > Package body created.
>
> > Elapsed: 00:00:00.04
> > SQL>
> > SQL> --
> > SQL> -- This is more fun with a test table
> > SQL> --
> > SQL>
> > SQL> create table date_tst ( start_dt date, end_dt date);
>
> > Table created.
>
> > Elapsed: 00:00:00.01
> > SQL>
> > SQL> insert into date_tst
> >   2  select date '2006-12-25' + dbms_random.value(1,365)
> >   3       , date '2007-12-25' + dbms_random.value(1,365)
> >   4  FROM   dual connect by level <= 4000;
>
> > 4000 rows created.
>
> > Elapsed: 00:00:00.15
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > Elapsed: 00:00:00.01
> > SQL>
> > SQL> --
> > SQL> --
> > SQL> -- Let's execute this mess
> > SQL> --
> > SQL> --
> > SQL>
> > SQL> set timing on autotrace traceonly
> > SQL>
> > SQL> select dates_pkg.minutes_elapsed(start_dt, end_dt) from date_tst;
>
> > 4000 rows selected.
>
> > Elapsed: 00:00:03.01
>
> > Execution Plan
> > ----------------------------------------------------------
> > Plan hash value: 2261420801
>
> > ---------------------------------------------------------------------------­­---
> > | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
> > Time     |
> > ---------------------------------------------------------------------------­­---
> > |   0 | SELECT STATEMENT  |          |  4000 | 72000 |     5   (0)|
> > 00:00:01 |
> > |   1 |  TABLE ACCESS FULL| DATE_TST |  4000 | 72000 |     5   (0)|
> > 00:00:01 |
> > ---------------------------------------------------------------------------­­---
>
> > Note
> > -----
> >    - dynamic sampling used for this statement
>
> > Statistics
> > ----------------------------------------------------------
> >       64014  recursive calls
> >           0  db block gets
> >         300  consistent gets
> >           0  physical reads
> >           0  redo size
> >       38634  bytes sent via SQL*Net to client
> >        2108  bytes received via SQL*Net from client
> >         268  SQL*Net roundtrips to/from client
> >           0  sorts (memory)
> >           0  sorts (disk)
> >        4000  rows processed
>
> > SQL>
> > SQL> select (end_dt - start_dt)*1440 from date_tst;
>
> > 4000 rows selected.
>
> > Elapsed: 00:00:00.21
>
> > Execution Plan
> > ----------------------------------------------------------
> > Plan hash value: 2261420801
>
> > ---------------------------------------------------------------------------­­---
> > | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
> > Time     |
> > ---------------------------------------------------------------------------­­---
> > |   0 | SELECT STATEMENT  |          |  4000 | 72000 |     5   (0)|
> > 00:00:01 |
> > |   1 |  TABLE ACCESS FULL| DATE_TST |  4000 | 72000 |     5   (0)|
> > 00:00:01 |
> > ---------------------------------------------------------------------------­­---
>
> > Note
> > -----
> >    - dynamic sampling used for this statement
>
> > Statistics
> > ----------------------------------------------------------
> >           4  recursive calls
> >           0  db block gets
> >         300  consistent gets
> >           0  physical reads
> >           0  redo size
> >      103258  bytes sent via SQL*Net to client
> >        2108  bytes received via SQL*Net from client
> >         268  SQL*Net roundtrips to/from client
> >           0  sorts
> > ...
>
> > read more »- Hide quoted text -
>
> > - Show quoted text -
>
> I think since the difference between two timestamps is a timestamp I
> would go this route:
>
> UT1 > l
>   1  select fld5, to_timestamp(sysdate) - fld5,
>   2         extract(day  from  to_timestamp(sysdate) - fld5 ) as Days,
>   3         extract(hour from  to_timestamp(sysdate) - fld5 ) as Hours
>   4* from marktest
> UT1 > /
>
> FLD5
> ---------------------------------------------------------------------------
> TO_TIMESTAMP(SYSDATE)-FLD5
> ---------------------------------------------------------------------------
>       DAYS      HOURS
> ---------- ----------
> 23-OCT-07 01.21.09.367316 PM
> +000000196 10:38:50.632684
>        196         10
>
> 23-OCT-07 01.21.17.584899 PM
> +000000196 10:38:42.415101
>        196         10
>
> 20-FEB-08 11.18.24.606839 AM
> +000000076 12:41:35.393161
>         76         12
>
> The difference is Days, hours, minutes, seconds, and fractions there
> of so it is human readable as is.  This can be useful.  If you need
> the components you can extract them or as Sybrand said look at the
> interval functions and also datatypes.
>
> HTH -- Mark D Powell --

The original question, at the top of your post and the top of this one, states it's a date field:

"question is simple... I have an date field in an table and I would like to
measure time difference (minutes or sec.) between current time and that
field."

 If the column truly is a date then my example works; if it's a timestamp then yours is the appropriate example.

Possibly the OP can clear this up?

David Fitzjarrell Received on Wed May 07 2008 - 15:45:04 CDT

Original text of this message