datetimediff in column expression

[Neico]

Hello,
I’m trying to convert the following code from Alteryx to Knime:

if [Row-1:Tail Number]=[Tail Number] AND [Origin IATA]=[Row-1:Destination IATA] then datetimediff([Gate Skd Departure Date-Time (UTC)],[Row-1:Gate Actual Arrival Date-Time (UTC)],“minutes”)else null() endif

Using, column expression, I created the following code snippet:

if(column(“Tail Number”,-1) == column(“Tail Number”) &&
column(“Origin IATA”) == column(“Destination IATA”, -1)){
1
} else {
null
}

Unfortunately, I cannot seem to figure out how to get the datetimediff argument to work. Any ideas?

Thank you!

[rfeigel]

There’s probably a more elegant way, but you could try converting your datetimes to UNIX time stamps then doing your calculations.

[Daniel_Weikert]

Not a javascript expert but have you already tried just subtract the to columns?
col1 -col2
br

[mlauber71]

@Neico I think the Column Expression node does not have a direct DateDiff function. What I did was I changed the condition and gave out the LAG-DateTime when the condition was met and then used a KNIME node.

date differences with LAG function from Column expressions – mlauber71

Another option would be to use a Group Loop to have the Tail Number in one group.

Also note you will have to activate the multi-row access.

[takbb]

Hi @Neico , it’s unfortunately the case that there is no date/time difference in Column Expressions, however this did make me wonder whether the same kind of “java hacking” that I have found works with String Manipulation also works with Column Expressions, and I’m pleased to say that it does. “Today I learned…”

So, whilst not ideal, you can write your own function within the column expression and make use of underlying java classes to help us out:

e.g.

function timeSerial(/*datetime*/ d)
/* returns Double */
{
    /* returns a datetime as the unix serial time 
     *  where d is a datetime
     */
    yy=getYear(d)-1900  /* constructor expects year less 1900 */
    mm=getMonthOfYear(d)-1 /* Jan = Month 0 */
    dd=getDayOfMonth(d)
    tm=substr(string(d),11) 
    if (length(tm)< 8) {
        /* if seconds are zero they aren't returned so just add some extra on in case!
         but if this is a zoned time, we need to ensure we don't pick up timezone info
         too, so take just the first 5 characters */
        tm=substr(tm,0,5) + ":00" }
    h=toInt(substr(tm,0,2))
    m=toInt(substr(tm,3,2))
    s=toInt(substr(tm,6,2))
    tm=new java.sql.Timestamp(yy,mm,dd,h,m,s,0)
    return tm.getTime() / 1000
}
function timeDiffSeconds(/*datetime*/ d1, /*datetime*/ d2) 
/* returns Double */
{
    /* returns the difference in seconds between d1 and d2 
     *  where d1 and d2 are both DateTime  
     */
    return timeSerial(d1)-timeSerial(d2)
}
function timeDiffDays(/*datetime*/ d1, /*datetime*/ d2)
/* returns Double */
{
    /* returns the difference in seconds between d1 and d2 
     *  where d1 and d2 are both DateTime 
     */
    return (timeSerial(d1)-timeSerial(d2)) / 86400
}

dt1=column("FirstDate")
dt2=column("SecondDate")

//timediff=timeDiffDays(dt2,dt1)
timediff = timeDiffSeconds(dt2,dt1)

Add the functions to your column expression. The first function “timeSerial” turns a date into a unix timestamp, you can then find the difference between two dates in seconds or days, depending on which other function you call
Calc Time Diff in Column Expressions.knwf (7.2 KB)

[NB. I’ve made a couple of corrections since first uploading, to allow for time being on the minute, and so lacking seconds, and also if a datetime with a timezone is passed. Not sure if the dateSerial is strictly returning a unix timestamp as to me it appears to me to be an hour out, but possibly that is because of my local timezone. Haven’t got time to investigate right now. However, it should be fine for comparison of dates]

[mlauber71]

@takbb impressive - this does look like some serious case of over-engineering, though

[takbb]

lol, @mlauber71 … I didn’t have the time available to under-engineer it