SEO

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?

On Wed, 7 May 2008 18:45:04 +0200, "buu" <aha@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

On May 7, 12:51*pm, sybra...@hccnet.nl wrote:
> On Wed, 7 May 2008 18:45:04 +0200, "buu" <a...@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

On May 7, 3:52*pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
> On May 7, 12:51*pm, sybra...@hccnet.nl wrote:
>
>
>
>
>
> > On Wed, 7 May 2008 18:45:04 +0200, "buu" <a...@a.com> wrote:
> > >question is simple... I have an date field in an table and I would liketo
> > >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 dateby
> converting it
> *12 * * * * *-- to a character string using the same format wewill 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 THATto a
> string
> *52 * * * * *-- of seconds and convert THAT to a number
> *53 * * * * *--
> *54 * * * * *-- Is it me, or are we essentially driving acrosstown 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 wonderfulfunction
> 100 * * * * *--
> 101 * * * * *-- So, okay, we take our date values and return theJulian
> 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 --

On May 7, 3:08*pm, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On May 7, 3:52*pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
>
> > On May 7, 12:51*pm, sybra...@hccnet.nl wrote:
>
> > > On Wed, 7 May 2008 18:45:04 +0200, "buu" <a...@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 subtractedthe
> > 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

On May 7, 4:45*pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
> On May 7, 3:08*pm, Mark D Powell <Mark.Pow...@eds.com> wrote:
>
>
>
> > On May 7, 3:52*pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
>
> > > On May 7, 12:51*pm, sybra...@hccnet.nl wrote:
>
> > > > On Wed, 7 May 2008 18:45:04 +0200, "buu" <a...@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 characterstring
> > > *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 andmake 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 timeportion 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 returnthe 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 ofthis 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.
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

I was thinking timestamp because of the subject line. It is probably
just a saved date so only simple date math is required though I alwyas
have a heck of a time constructing simple date math when you want
hours, minutes, seconds broken out. Eventually I get the mods,
commas, and parenthesis correct.

-- Mark D Powell --