I need help on converting a string to proper Date & Time Stamp data type.
Some example of the string field are:
Column: StartTime
2019-01-15T13:13:42.22054Z
2019-01-07T09:00:16.3318899Z
2018-12-13T11:49:11.8787989Z
2019-01-08T11:59:16.5398Z
2019-01-10T14:14:18.514Z
2018-12-12T15:10:31.392112Z
I used ‘string to Date & Time’ and the node says, “no suitable format found!”, therefore it does not executes successfully. I tried all the other date & time nodes that exist in KNIME and no one seems to do what I need.
The only that works is “DateExtractor” which gives me two new columns, in addtion to StartTime.
There are:
Date Column with the right format eg. 2018-12-27T13:06:19
and Format column eg. YYYY-MM-DDTHH:MM:SS+HH:MM
How can I use this result to bring it back to my initial table? or is there anyone else with the same problem that did something different?
hm. I solved it. I realized the problem is this milliseconds after the “.”
So, I excluded them, by using “.” as a delimeter in the cell splitter and the I used a string to date & time new column and it worked.
If you do not wish to lose milliseconds they should all have the same number of digits in order for String to Date&Time node to convert them. The following date format can do it:
yyyy-MM-dd'T'HH:mm[:ss[.SSS]]VV['['zzzz']']
where in this case all strings should have 3 digits (.SSS)
Hi @ipazin
Do the strings have to all have 3 digits? I have come across a strange situation this morning with a colleague who I shared a workflow with. I have a variable created via the Date&Time Widget that captures the execution time (date_input) which is then used in a RuleEngine to decide the content of a column.
If the date is older than the execution time the variable is used otherwise another datetime column value is taken.
The resulting string is then changed to a datetime. In my workflow - this works smoothly with the following
yyyy-MM-dd'T'HH:mm[:ss[.SSS]]
It copes happily with all three of these scenarios:
However my colleague received an error which we could only solve by using:
yyyy-MM-dd'T'HH:mm[:ss[.S[SS]]]
which worked perfectly for her but then didn’t at all work for my data!!
I thought that using the square brackets allowed for characters whether they were there or not…
you are welcome. No problem. By default search topics are sorted by relevance so if would be good to know how this relevance is calculated actually. Anyways glad to add that thread to this one cause this one seems more relevant
As my variable is created slightly differently than @jan_lender, I used @SimonS suggestion (which works and feels at least logical compared to what I had managed!)
I’ll have a look at using Create Date&Time Range instead of the widget. But if I use the variable in the Rule Engine I fear I will still have to use String to Date&Time anyway as I’m not aware I can have anything but a String in this node:
Date&Time data type is stored as flow variable type string. This means if you use it in Rule Engine your output column will be type string. From Rule Engine node description: “The type of the outcome column is the common super type of all possible outcomes (including the rules that can never match).”