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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 7 May 2008 13:08:27 -0700 (PDT)
Message-ID: <41f6bc40-04fe-4acf-9438-879a9988e16a@z72g2000hsb.googlegroups.com>


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 -- Received on Wed May 07 2008 - 15:08:27 CDT

Original text of this message