Re: how to count elapsed time between sysdate and some saved timestamp?
Date: Wed, 7 May 2008 12:52:06 -0700 (PDT)
Message-ID: <bcda02ec-c66b-4693-a073-1a6788317341@c58g2000hsc.googlegroups.com>
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 (memory)
0 sorts (disk)
4000 rows processed
SQL>
SQL> --
SQL> -- Let's hose up that last function
SQL> -- by passing the arguments in reverse
SQL> -- order
SQL> --
SQL> -- Maybe the original author should have
SQL> -- provided some parameter checking code
SQL> --
SQL>
SQL> select dates_pkg.minutes_elapsed(end_dt, start_dt) from date_tst;
4000 rows selected.
Elapsed: 00:00:03.04
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
64004 recursive calls
0 db block gets
300 consistent gets
0 physical reads
0 redo size
42634 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 (start_dt - end_dt)*1440 from date_tst;
4000 rows selected.
Elapsed: 00:00:00.23
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
103470 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> There is nothing more satisfying than a job well done.
</sarcasm>
To be honest I can't understand why you don't use:
select (date2 -date1)*1440 from ...
to get the minutes between the two dates, or:
select (date2 - date1)*86400 from ...
to return the number of seconds between the dates.
Both have worked for years and continue to do so.
David Fitzjarrell Received on Wed May 07 2008 - 14:52:06 CDT