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
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
> =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
date=
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
str=
ing
> =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,'M=
M/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
but =
we changed
> it to
> =A027 =A0 =A0 =A0 =A0 =A0-- a character string so we could go back and
mak=
e 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
****=
tion of
> it,
> =A051 =A0 =A0 =A0 =A0 =A0-- convert that BACK to a date, then convert
THAT=
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
across=
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(timevalue,=
'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
secon=
ds 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
seconds =
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
mi=
nutes
> 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
functi=
on 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
wonderful=
function
> 100 =A0 =A0 =A0 =A0 =A0--
> 101 =A0 =A0 =A0 =A0 =A0-- So, okay, we take our date values and return
the=
Julian
> 102 =A0 =A0 =A0 =A0 =A0-- representations of them using all of the
mathema=
tical
> 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
subtr=
acting
> 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
Julia=
n 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
t=
he
> 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
thi=
s 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
mathemati=
cal
> 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---
> | Id =A0| Operation =A0 =A0 =A0 =A0 | Name =A0 =A0 | Rows =A0| Bytes |
Cos=
t (%CPU)|
> Time =A0 =A0 |
>
--------------------------------------------------------------------------=
-=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---
>
> 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---
> | Id =A0| Operation =A0 =A0 =A0 =A0 | Name =A0 =A0 | Rows =A0| Bytes |
Cos=
t (%CPU)|
> Time =A0 =A0 |
>
--------------------------------------------------------------------------=
-=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---
>
> 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
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 --
|
