2 Replies Latest reply: Oct 3, 2011 8:58 AM by Andries Triegaardt RSS

    Subtracting specific number of hours from timestamp

    Andries Triegaardt

      Hi.

       

      I am translating some SQL into QlikView script. In the SQL script exactly ONE hour is subtracted from a time stamp using

      dateadd(hour, -1, 'YYYY-MM-DD HH:mm:ss.fff')

       

      I want to do the same in the QV script, but don't know how to. The addmonths won't work, neither will the interval function, as I am not trying to subtract two time stamps from one another, rather a number of hours (integer) from a time stamp.

       

      // Say my original time stamp is 2011-10-03 04:19:17.000

      // I want to display XXX as the value 2011-10-03 03:19:17

      // Notice that I've dropped the fraction and the hour is one less than the original.

       

      // Here I am forcing QlikView to see the field TS_EVENT as a time value.

      let zTS_EVENT = 'time#(TS_EVENT, ' & chr(39) & 'YYYY-MM-DD HH:mm:ss' & chr(39) & ')';

       

       

      // I've triend all these methods:

       

      TIMES:

      load

      time(hour($(zTS_EVENT)-1 & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss') as XXX

      from ..................

       

      // as well as

       

      TIMES:

      load

      time(hour($(zTS_EVENT)-time('01', 'HH') & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss') as XXX

      from ..................

       

      // as well as

       

      TIMES:

      load

      time(hour($(zTS_EVENT) & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss') - time('01:00:00', 'HH:mm:ss') as XXX

      from ..................

       

      // but none seem to work.

       

      PLEASE HELP!