Re: how to count elapsed time between sysdate and some saved timestamp?
Date: Wed, 7 May 2008 17:27:53 -0700 (PDT)
Message-ID: <2050f831-f205-47bb-a801-a36a32b1d5b7@x41g2000hsb.googlegroups.com>
On May 7, 4:45 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On May 7, 3:08 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
> > On May 7, 3:52 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
> > > 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.
>
> ...
>
> 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 --