Subtracting specific number of hours from timestamp

[andries_g_t]

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!

[lironbaram]

hei

try this one

timestamp(num(timestamporig)-1/24) as new

[andries_g_t · Accepted solution]

Thanks!

I came right with this:

if(minute($(zTS_EVENT)) = '00' and second($(zTS_EVENT)) = '00', time((hour($(zTS_EVENT)) & ':00:00' - 1/24), 'HH:mm:ss'),

time(hour($(zTS_EVENT)) & ':00:00', 'HH:mm:ss')) as TS_START,

if(minute($(zTS_EVENT)) = '00' and second($(zTS_EVENT)) = '00', time(hour($(zTS_EVENT)) & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss'),

time((hour($(zTS_EVENT)) & ':00:00' + 1/24), 'HH:mm:ss')) as TS_END,

My if statement’s condition didn’t like the ’00:00’ so I broke it up into separate minutes and seconds

Andries Triegaardt