datetimediff in column expression

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)){
} else {

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

Thank you!

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

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

@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.

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.

1 Like

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. :wink: “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:


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 */
    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" }
    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


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 :slight_smile:
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]


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

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

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.