Create a date based in microseconds UTC

Hi all,

Playing with DCM/DBM data ...again...

Well, here is my challenge: I want to have a column with a specific date and time using a column that has time in DOUBLE format and the Time is in microseconds since 1970-01-01 00:00:00 UTC.

Any help?



I tried NUMBER TO STRING and them STRING TO DATE but still don't work:

Hi Fabio,

Due to the java library KNIME currently uses for Dates, microseconds will not work. There are generally two options here.

First, you can convert to milliseconds by removing the last digits and then use a Java Snippet node to create a Date and Time column from the milliseconds.

Second (easier, but less pecise), you convert to seconds and then use the Date/Time Shift node. There, you select "Use shift value from column", select your column, and use seconds for granularity of shift. For Date reference, select "Use fixed date" and enter 1970-1-1. Note that while this node actually can use milliseconds, it can only take an integer as input, and the milliseconds since 1970 have too many digits to be represented as integer.

Hope that helps!




Worked perfectly well!

Thank you!

What attribute(double, integer, the string did you use for Date/Time Shift node for time

Hi there @rkagrawal,

can you explain a bit more what is troubling you? Are you also creating date based in microseconds or just trying to use Date&Time Shift node?


1 Like

Hello @ipazin,

Thank You for responding to my request. Please see the attached document.
I want to convert the time columns (units are in seconds.milliseconds) to Knime recognized time unit possibly in milliseconds. Advise what options do I have to convert the time columns so that I use the options such as “window loop start” node

Thank You!

Forum-E1.xls (33.5 KB)

Why do you want to use a “time unit” in KNIME? The values in this spread sheet are double/float values, starting at around 100ms - this is meaningless in a “time unit”. A time unit is a moment in clock time like “12:23 PM”; if you wanted to say something like “import this spreadsheet and use 1:00 AM as a starting point” then you could do some operation that creates a time unit - but again - why?

I meant a unit of time such as milliseconds.
Reason: windowing node accepts units such as second, milliseconds.
I believe you are referring to converting to the duration of the day
And then yes I am ok about converting it to duration as well.
As you very well point the day it is in double/float format and I am looking for nodes to convert it to time in hh:mm:ss: ms as the smallest unit is in ms and I want the nodes in Knime to recognize the time column and recognize it appropriately.
Conversion will allow me to perform analysis such as using plot time vs other parameters

I hope above explanation clarifies

Since actual time is not important, you can treat the seconds/ms as time since epoch and do this:
rk time.knwf (17.3 KB)


Wow! this is called a beautiful solution.

Thank You!

1 Like