title: Oracle Jobs & finding timestamp difference
body: <p>I've faced today a weird problem: I have a function which is called by job. I want to find the difference from start of the function till the end to log it then to some table.</p>
  
  <p>So, let's say I have function</p>
  
  <pre><code>procedure p is
    starttime    timestamp := systimestamp;
  
    procedure writeTime
      is
        diff         interval day to second := systimestamp - starttime;
    begin
      -- here insert diff to some table
    end;
  begin
    -- doing some long stuff
  
    writeTime();
  exception
    when others then
      writeTime();
  end;
  </code></pre>
  
  <p>The problem in the function is:</p>
  
  <ol>
  <li><p>When I run this manually, it works well, difference is clear. E.g. I use <em>extract</em> to parse the interval: <code>extract(hour from diff)*60*60 + extract(minute from diff)*60 + extract(second from diff)</code></p></li>
  <li><p>When I set up the job and the job runs this function I have a big problem: it returns negative result, which as I, after some tests, understand is generated here <code>systimestamp - starttime</code>. Seems like systimestamp in this calculation is taken from Greenwich timezone, and mine is one hour bigger, so this calculation <code>diff         interval day to second := systimestamp - starttime;</code> is returning the value like (-1 hour + difference).</p></li>
  </ol>
  
  <p>By stupid brute-forcing I've found a solution: </p>
  
  <pre><code>procedure p is
    starttime    timestamp := systimestamp;
  
    procedure writeTime
      is
        diff         interval day to second;
        endtime      timestamp := systimestamp;
    begin
      diff := endtime - starttime;
  
      -- here insert diff to some table
    end;
  begin
    -- doing some long stuff
  
    writeTime();
  exception
    when others then
      writeTime();
  end;
  </code></pre>
  
  <p>which simply writes systimestamp in the variable first, and only then calculates the difference.</p>
  
  <p>My database parameters:</p>
  
  <ul>
  <li>Oracle 11.2.0.2.0</li>
  <li>Timezone +1 Berlin</li>
  </ul>
  
  <p>So now the question: I really want to know is it a bug of my RDBMS or perhaps I do not see some obvious explanation why it is like that? The concrete question is: why during this operation</p>
  
  <pre><code>starttime    timestamp := systimestamp;
  </code></pre>
  
  <p>it takes one timezone and during this</p>
  
  <pre><code>diff         interval day to second := systimestamp - starttime;
  </code></pre>
  
  <p>it takes another one in the same procedure of the same session with the same settings?</p>