On May 7, 12:51=A0pm, sybra...@[EMAIL PROTECTED]
wrote:
> On Wed, 7 May 2008 18:45:04 +0200, "buu" <a...@[EMAIL PROTECTED]
> wrote:
> >question is simple... I have an date field in an table and I would like
t=
o
> >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 ****tion 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 <=3D 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
|
