On May 7, 3:08=A0pm, Mark D Powell <Mark.Pow...@[EMAIL PROTECTED]
> wrote:
> On May 7, 3:52=A0pm, "fitzjarr...@[EMAIL PROTECTED]
" <orat...@[EMAIL PROTECTED]
> wrote:
>
> > 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
li=
ke to
> > > >measure time difference (minutes or sec.) between current time and
th=
at
> > > >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
> > =A0 2 =A0AS
> > =A0 3 =A0 =A0 =A0FUNCTION julian_date
> > =A0 4 =A0 =A0 =A0 =A0 =A0( date_to_convert DATE )
> > =A0 5 =A0 =A0 =A0 =A0 =A0RETURN NUMBER;
> > =A0 6
> > =A0 7 =A0 =A0 =A0FUNCTION minutes_since_midnight
> > =A0 8 =A0 =A0 =A0 =A0 =A0( timevalue DATE )
> > =A0 9 =A0 =A0 =A0 =A0 =A0RETURN NUMBER;
> > =A010
> > =A011 =A0 =A0 =A0FUNCTION minutes_elapsed
> > =A012 =A0 =A0 =A0 =A0 =A0( lowdate DATE
> > =A013 =A0 =A0 =A0 =A0 =A0, highdate DATE )
> > =A014 =A0 =A0 =A0 =A0 =A0RETURN NUMBER;
> > =A015
> > =A016 =A0END dates_pkg;
> > =A017 =A0/
>
> > Package created.
>
> > Elapsed: 00:00:00.01
> > SQL>
> > SQL> CREATE PACKAGE BODY dates_pkg
> > =A0 2 =A0AS
> > =A0 3 =A0 =A0 =A0FUNCTION julian_date
> > =A0 4 =A0 =A0 =A0 =A0 =A0( date_to_convert DATE)
> > =A0 5 =A0 =A0 =A0 =A0 =A0RETURN NUMBER
> > =A0 6 =A0 =A0 =A0IS
> > =A0 7 =A0 =A0 =A0 =A0 =A0varch_value VARCHAR (10);
> > =A0 8 =A0 =A0 =A0 =A0 =A0num_value NUMBER (20);
> > =A0 9 =A0 =A0 =A0BEGIN
> > =A010 =A0 =A0 =A0 =A0 =A0--
> > =A011 =A0 =A0 =A0 =A0 =A0-- First, we take a date and convert it to a
da=
te by
> > converting it
> > =A012 =A0 =A0 =A0 =A0 =A0-- to a character string using the same
format =
we will use
> > to
> > =A013 =A0 =A0 =A0 =A0 =A0-- convert it BACK to a date again
> > =A014 =A0 =A0 =A0 =A0 =A0--
> > =A015 =A0 =A0 =A0 =A0 =A0-- Oh, then we convert it back to a character
s=
tring
> > =A016 =A0 =A0 =A0 =A0 =A0--
> > =A017 =A0 =A0 =A0 =A0 =A0-- In Julian format, which is a number
> > =A018 =A0 =A0 =A0 =A0 =A0--
> > =A019 =A0 =A0 =A0 =A0 =A0SELECT TO_CHAR
> > =A020 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 (
TO_DATE(TO_CHAR(date_to_convert,=
'MM/DD/YYYY'),'MM/DD/
> > YYYY')
> > =A021 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 , 'J')
> > =A022 =A0 =A0 =A0 =A0 =A0INTO =A0 varch_value
> > =A023 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > =A024
> > =A025 =A0 =A0 =A0 =A0 =A0--
> > =A026 =A0 =A0 =A0 =A0 =A0-- Okay, so we had a Julian date as a number
bu=
t we changed
> > it to
> > =A027 =A0 =A0 =A0 =A0 =A0-- a character string so we could go back and
m=
ake it a ...
> > =A028 =A0 =A0 =A0 =A0 =A0-- NUMBER ... again
> > =A029 =A0 =A0 =A0 =A0 =A0--
> > =A030 =A0 =A0 =A0 =A0 =A0SELECT TO_NUMBER (varch_value)
> > =A031 =A0 =A0 =A0 =A0 =A0INTO =A0 num_value
> > =A032 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > =A033
> > =A034 =A0 =A0 =A0 =A0 =A0--
> > =A035 =A0 =A0 =A0 =A0 =A0-- So, we finally make up our mind and keep
it =
a number and
> > =A036 =A0 =A0 =A0 =A0 =A0-- return it from the function
> > =A037 =A0 =A0 =A0 =A0 =A0--
> > =A038 =A0 =A0 =A0 =A0 =A0RETURN (num_value);
> > =A039 =A0 =A0 =A0END julian_date;
> > =A040
> > =A041
> > =A042 =A0 =A0 =A0FUNCTION minutes_since_midnight (
> > =A043 =A0 =A0 =A0 =A0 =A0timevalue DATE)
> > =A044 =A0 =A0 =A0 =A0 =A0RETURN NUMBER
> > =A045 =A0 =A0 =A0IS
> > =A046 =A0 =A0 =A0 =A0 =A0secs_elapsed NUMBER (20);
> > =A047 =A0 =A0 =A0 =A0 =A0mins_elapsed NUMBER (20);
> > =A048 =A0 =A0 =A0BEGIN
> > =A049 =A0 =A0 =A0 =A0 =A0--
> > =A050 =A0 =A0 =A0 =A0 =A0-- So now we take a date and extract the time
p=
ortion of
> > it,
> > =A051 =A0 =A0 =A0 =A0 =A0-- convert that BACK to a date, then convert
TH=
AT to a
> > string
> > =A052 =A0 =A0 =A0 =A0 =A0-- of seconds and convert THAT to a number
> > =A053 =A0 =A0 =A0 =A0 =A0--
> > =A054 =A0 =A0 =A0 =A0 =A0-- Is it me, or are we essentially driving
acro=
ss town just
> > to
> > =A055 =A0 =A0 =A0 =A0 =A0-- go next door?
> > =A056 =A0 =A0 =A0 =A0 =A0--
> > =A057 =A0 =A0 =A0 =A0 =A0SELECT TO_NUMBER
> > =A058 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 (
TO_CHAR(TO_DATE(TO_CHAR(timevalu=
e,'HH:MI AM'),'HH:MI AM')
> > =A059 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 , 'SSSSS') )
> > =A060 =A0 =A0 =A0 =A0 =A0INTO =A0 secs_elapsed
> > =A061 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > =A062
> > =A063 =A0 =A0 =A0 =A0 =A0--
> > =A064 =A0 =A0 =A0 =A0 =A0-- Oooo, now we divide that total number of
sec=
onds by ...
> > =A065 =A0 =A0 =A0 =A0 =A0-- wait for it ...
> > =A066 =A0 =A0 =A0 =A0 =A0-- any second now ...
> > =A067 =A0 =A0 =A0 =A0 =A0-- 60! =A0Who would have thought that 60
second=
s equals
> > =A068 =A0 =A0 =A0 =A0 =A0-- one minute?
> > =A069 =A0 =A0 =A0 =A0 =A0--
> > =A070 =A0 =A0 =A0 =A0 =A0SELECT (secs_elapsed / 60)
> > =A071 =A0 =A0 =A0 =A0 =A0INTO =A0 mins_elapsed
> > =A072 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > =A073
> > =A074 =A0 =A0 =A0 =A0 =A0--
> > =A075 =A0 =A0 =A0 =A0 =A0-- Before we rest on our laurels we return
the =
minutes
> > since midnight
> > =A076 =A0 =A0 =A0 =A0 =A0--
> > =A077 =A0 =A0 =A0 =A0 =A0RETURN (mins_elapsed);
> > =A078 =A0 =A0 =A0END minutes_since_midnight;
> > =A079
> > =A080
> > =A081 =A0 =A0 =A0FUNCTION minutes_elapsed
> > =A082 =A0 =A0 =A0 =A0 =A0( lowdate DATE
> > =A083 =A0 =A0 =A0 =A0 =A0, highdate DATE )
> > =A084 =A0 =A0 =A0 =A0 =A0RETURN NUMBER
> > =A085 =A0 =A0 =A0IS
> > =A086 =A0 =A0 =A0 =A0 =A0final_number NUMBER (20);
> > =A087 =A0 =A0 =A0 =A0 =A0low_julian NUMBER (20);
> > =A088 =A0 =A0 =A0 =A0 =A0high_julian NUMBER (20);
> > =A089 =A0 =A0 =A0 =A0 =A0num_days NUMBER (20);
> > =A090 =A0 =A0 =A0 =A0 =A0num_minutes NUMBER (20);
> > =A091 =A0 =A0 =A0 =A0 =A0temp_mins NUMBER (20);
> > =A092 =A0 =A0 =A0 =A0 =A0min_low NUMBER (20);
> > =A093 =A0 =A0 =A0 =A0 =A0min_high NUMBER (20);
> > =A094 =A0 =A0 =A0BEGIN
> > =A095 =A0 =A0 =A0 =A0 =A0--
> > =A096 =A0 =A0 =A0 =A0 =A0-- Now, why didn't we use this julian_date
func=
tion in the
> > =A097 =A0 =A0 =A0 =A0 =A0-- last installment of Julian conversions?
> > =A098 =A0 =A0 =A0 =A0 =A0--
> > =A099 =A0 =A0 =A0 =A0 =A0-- Oh, yeah, because we just WROTE that
wonderf=
ul function
> > 100 =A0 =A0 =A0 =A0 =A0--
> > 101 =A0 =A0 =A0 =A0 =A0-- So, okay, we take our date values and return
t=
he Julian
> > 102 =A0 =A0 =A0 =A0 =A0-- representations of them using all of the
mathe=
matical
> > 103 =A0 =A0 =A0 =A0 =A0-- aerobics from earlier
> > 104 =A0 =A0 =A0 =A0 =A0--
> > 105 =A0 =A0 =A0 =A0 =A0-- I guess this is so much easier than simply
sub=
tracting
> > 106 =A0 =A0 =A0 =A0 =A0-- them
> > 107 =A0 =A0 =A0 =A0 =A0--
> > 108 =A0 =A0 =A0 =A0 =A0SELECT julian_date (lowdate)
> > 109 =A0 =A0 =A0 =A0 =A0INTO =A0 low_julian
> > 110 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 111
> > 112 =A0 =A0 =A0 =A0 =A0SELECT julian_date (highdate)
> > 113 =A0 =A0 =A0 =A0 =A0INTO =A0 high_julian
> > 114 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 115
> > 116 =A0 =A0 =A0 =A0 =A0--
> > 117 =A0 =A0 =A0 =A0 =A0-- Woo-hoo! Higher math time! =A0Subtract the
Jul=
ian dates
> > 118 =A0 =A0 =A0 =A0 =A0-- and get the number of days
> > 119 =A0 =A0 =A0 =A0 =A0--
> > 120 =A0 =A0 =A0 =A0 =A0-- Isn't that what we'd get if we just
subtracted=
the
> > 121 =A0 =A0 =A0 =A0 =A0-- submitted dates as-is?
> > 122 =A0 =A0 =A0 =A0 =A0--
> > 123 =A0 =A0 =A0 =A0 =A0-- Of course it is
> > 124 =A0 =A0 =A0 =A0 =A0--
> > 125 =A0 =A0 =A0 =A0 =A0SELECT (high_julian - low_julian)
> > 126 =A0 =A0 =A0 =A0 =A0INTO =A0 num_days
> > 127 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 128
> > 129 =A0 =A0 =A0 =A0 =A0--
> > 130 =A0 =A0 =A0 =A0 =A0-- Now we calculate the total minutes elapsed
> > 131 =A0 =A0 =A0 =A0 =A0-- using our values generated by our extreme
> > 132 =A0 =A0 =A0 =A0 =A0-- gyrations
> > 133 =A0 =A0 =A0 =A0 =A0--
> > 134 =A0 =A0 =A0 =A0 =A0-- I'm out of breath just thinking about all of
t=
his work
> > 135 =A0 =A0 =A0 =A0 =A0--
> > 136 =A0 =A0 =A0 =A0 =A0SELECT (num_days * 1440)
> > 137 =A0 =A0 =A0 =A0 =A0INTO =A0 num_minutes
> > 138 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 139
> > 140 =A0 =A0 =A0 =A0 =A0--
> > 141 =A0 =A0 =A0 =A0 =A0-- And now we put those other mathematical
moves
> > 142 =A0 =A0 =A0 =A0 =A0-- to use
> > 143 =A0 =A0 =A0 =A0 =A0--
> > 144 =A0 =A0 =A0 =A0 =A0-- Tell me again why we think we're smarter
than
> > 145 =A0 =A0 =A0 =A0 =A0-- the average bear?
> > 146 =A0 =A0 =A0 =A0 =A0--
> > 147 =A0 =A0 =A0 =A0 =A0SELECT minutes_since_midnight (lowdate)
> > 148 =A0 =A0 =A0 =A0 =A0INTO =A0 min_low
> > 149 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 150
> > 151 =A0 =A0 =A0 =A0 =A0SELECT minutes_since_midnight (highdate)
> > 152 =A0 =A0 =A0 =A0 =A0INTO =A0 min_high
> > 153 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 154
> > 155 =A0 =A0 =A0 =A0 =A0--
> > 156 =A0 =A0 =A0 =A0 =A0-- Now this is disgusting
> > 157 =A0 =A0 =A0 =A0 =A0--
> > 158 =A0 =A0 =A0 =A0 =A0-- Using a TEMP variable to aid in simple
mathema=
tical
> > 159 =A0 =A0 =A0 =A0 =A0-- processing
> > 160 =A0 =A0 =A0 =A0 =A0--
> > 161 =A0 =A0 =A0 =A0 =A0SELECT (min_high - min_low)
> > 162 =A0 =A0 =A0 =A0 =A0INTO =A0 temp_mins
> > 163 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 164
> > 165 =A0 =A0 =A0 =A0 =A0--
> > 166 =A0 =A0 =A0 =A0 =A0-- And this is better than:
> > 167 =A0 =A0 =A0 =A0 =A0-- select (end_date - start_date)*1440 because?
> > 168 =A0 =A0 =A0 =A0 =A0--
> > 169 =A0 =A0 =A0 =A0 =A0SELECT (num_minutes + temp_mins)
> > 170 =A0 =A0 =A0 =A0 =A0INTO =A0 final_number
> > 171 =A0 =A0 =A0 =A0 =A0FROM =A0 dual;
> > 172
> > 173 =A0 =A0 =A0 =A0 =A0RETURN (final_number);
> > 174
> > 175 =A0 =A0 =A0END minutes_elapsed;
> > 176 =A0END dates_pkg;
> > 177 =A0/
>
> > 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
> > =A0 2 =A0select date '2006-12-25' + dbms_random.value(1,365)
> > =A0 3 =A0 =A0 =A0 , date '2007-12-25' + dbms_random.value(1,365)
> > =A0 4 =A0FROM =A0 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
>
> >
------------------------------------------------------------------------=
---=AD=AD---
> > | Id =A0| Operation =A0 =A0 =A0 =A0 | Name =A0 =A0 | Rows =A0| Bytes |
C=
ost (%CPU)|
> > Time =A0 =A0 |
> >
------------------------------------------------------------------------=
---=AD=AD---
> > | =A0 0 | SELECT STATEMENT =A0| =A0 =A0 =A0 =A0 =A0| =A04000 | 72000 |
=
=A0 =A0 5 =A0 (0)|
> > 00:00:01 |
> > | =A0 1 | =A0TABLE ACCESS FULL| DATE_TST | =A04000 | 72000 | =A0 =A0 5
=
=A0 (0)|
> > 00:00:01 |
> >
------------------------------------------------------------------------=
---=AD=AD---
>
> > Note
> > -----
> > =A0 =A0- dynamic sampling used for this statement
>
> > Statistics
> > ----------------------------------------------------------
> > =A0 =A0 =A0 64014 =A0recursive calls
> > =A0 =A0 =A0 =A0 =A0 0 =A0db block gets
> > =A0 =A0 =A0 =A0 300 =A0consistent gets
> > =A0 =A0 =A0 =A0 =A0 0 =A0physical reads
> > =A0 =A0 =A0 =A0 =A0 0 =A0redo size
> > =A0 =A0 =A0 38634 =A0bytes sent via SQL*Net to client
> > =A0 =A0 =A0 =A02108 =A0bytes received via SQL*Net from client
> > =A0 =A0 =A0 =A0 268 =A0SQL*Net roundtrips to/from client
> > =A0 =A0 =A0 =A0 =A0 0 =A0sorts (memory)
> > =A0 =A0 =A0 =A0 =A0 0 =A0sorts (disk)
> > =A0 =A0 =A0 =A04000 =A0rows 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
>
> >
------------------------------------------------------------------------=
---=AD=AD---
> > | Id =A0| Operation =A0 =A0 =A0 =A0 | Name =A0 =A0 | Rows =A0| Bytes |
C=
ost (%CPU)|
> > Time =A0 =A0 |
> >
------------------------------------------------------------------------=
---=AD=AD---
> > | =A0 0 | SELECT STATEMENT =A0| =A0 =A0 =A0 =A0 =A0| =A04000 | 72000 |
=
=A0 =A0 5 =A0 (0)|
> > 00:00:01 |
> > | =A0 1 | =A0TABLE ACCESS FULL| DATE_TST | =A04000 | 72000 | =A0 =A0 5
=
=A0 (0)|
> > 00:00:01 |
> >
------------------------------------------------------------------------=
---=AD=AD---
>
> > Note
> > -----
> > =A0 =A0- dynamic sampling used for this statement
>
> > Statistics
> > ----------------------------------------------------------
> > =A0 =A0 =A0 =A0 =A0 4 =A0recursive calls
> > =A0 =A0 =A0 =A0 =A0 0 =A0db block gets
> > =A0 =A0 =A0 =A0 300 =A0consistent gets
> > =A0 =A0 =A0 =A0 =A0 0 =A0physical reads
> > =A0 =A0 =A0 =A0 =A0 0 =A0redo size
> > =A0 =A0 =A0103258 =A0bytes sent via SQL*Net to client
> > =A0 =A0 =A0 =A02108 =A0bytes received via SQL*Net from client
> > =A0 =A0 =A0 =A0 268 =A0SQL*Net roundtrips to/from client
> > =A0 =A0 =A0 =A0 =A0 0 =A0sorts
> > ...
>
> > read more =BB- Hide quoted text -
>
> > - Show quoted text -
>
> I think since the difference between two timestamps is a timestamp I
> would go this route:
>
> UT1 > l
> =A0 1 =A0select fld5, to_timestamp(sysdate) - fld5,
> =A0 2 =A0 =A0 =A0 =A0 extract(day =A0from =A0to_timestamp(sysdate) -
fld5 =
) as Days,
> =A0 3 =A0 =A0 =A0 =A0 extract(hour from =A0to_timestamp(sysdate) - fld5
) =
as Hours
> =A0 4* from marktest
> UT1 > /
>
> FLD5
>
--------------------------------------------------------------------------=
-
> TO_TIMESTAMP(SYSDATE)-FLD5
>
--------------------------------------------------------------------------=
-
> =A0 =A0 =A0 DAYS =A0 =A0 =A0HOURS
> ---------- ----------
> 23-OCT-07 01.21.09.367316 PM
> +000000196 10:38:50.632684
> =A0 =A0 =A0 =A0196 =A0 =A0 =A0 =A0 10
>
> 23-OCT-07 01.21.17.584899 PM
> +000000196 10:38:42.415101
> =A0 =A0 =A0 =A0196 =A0 =A0 =A0 =A0 10
>
> 20-FEB-08 11.18.24.606839 AM
> +000000076 12:41:35.393161
> =A0 =A0 =A0 =A0 76 =A0 =A0 =A0 =A0 12
>
> The difference is Days, hours, minutes, seconds, and fractions there
> of so it is human readable as is. =A0This can be useful. =A0If 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
|
