String to Date & Timestamp

Hi,

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.

2 Likes

Thanks for posting the solution! Hopefully it will help others who find this topic in the future.

Hi there!

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) :wink:

Br,
Ivan

Easier said than done.

The problem is that I have in the same columns cells like:

1.00:01:41.6780000
12.00:23:12.678436780
432.10:11:15.4367894
15:20:33
25:15:23.49856961

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:
image

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…

Hi @zedleb,

no they actually don’t. Though so before this topic :smiley:

Br,
Ivan

2 Likes

Great pointer many thanks! That thread has it covered (I didn’t see it in my search :blush: I’ll use one of the suggestions in there.

1 Like

Hi @zedleb,

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 :smiley:

Br,
Ivan

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:

Hi @zedleb,

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).”

So you’ll have to use String to Date&Time again.

Br,
Ivan

2 Likes

Hi @ipazin
Thanks for confirming - that makes sense.