Hello KNIME Community!
Trust everyone doing good.
I have two string columns where date and time values with time zones are present. Need to convert those into date and time datatype. I tried with yyyy-MM-dd HH:mm:ss.[SSS] Z using String to Date&Time node. However, still getting error. Reference table attached below - kindly help.
2008-08-01 16:45:58.8 UTC
2008-08-08 12:23:43.717 UTC
2008-08-09 22:34:02.267 UTC
2008-08-12 08:51:20.96 UTC
2008-08-14 02:39:38.667 UTC
2008-08-14 14:00:08.087 UTC
2008-08-18 14:21:43.443 UTC
2008-08-20 03:36:11.473 UTC
2008-08-27 16:49:51.413 UTC
2008-09-01 01:16:46.44 UTC
2008-09-01 14:18:27.543 UTC
2008-09-02 11:59:43.763 UTC
2008-09-11 13:36:07.73 UTC
2008-09-15 12:29:38.807 UTC
2008-09-15 12:36:09.667 UTC
2008-09-15 16:20:55.46 UTC
2008-09-15 19:04:53.367 UTC
2008-09-15 23:54:30.83 UTC
2008-09-16 06:44:41.81 UTC
2008-09-16 11:04:05.557 UTC
2008-09-16 15:54:58.837 UTC
@omprakashjena welcome to the KNIME forum. The problem seem to be the first line with only one number behind a second. I would have expected the [SSS] part to fix that but it did not.
One quick fix was a combination of settings
yyyy-MM-dd HH:mm:ss.[SSS] zzzz
yyyy-MM-dd HH:mm:ss.S[SSSS] zzzz
Maybe not the most elegant way but it should do the job:
Thanks a lot for the solution, @mlauber71! This really helps.
Hi @omprakashjena ,
As an additional note, you can actually mix-and-match formats into a single format mask, using a the series of optional masks (in square brackets) as per @mlauber71 's example.
This should mean just a single Date&Time node without the need to re-join and additional rules for missing values.
For example, I think that if you used the following it would match 3 digit, 2 digit or 1 digit milliseconds as appropriate, and correctly parse all of your date/times in a single hit:
yyyy-MM-dd HH:mm:ss.[SSS][SS][S] zzzz
(the important trick is that the optional masks MUST be specified in descending order of length, so as to attempt the most-selective
SSS first, before failing over to attempting
SS and finally
The downside is that if you had “bad” data that somehow matched all of the optional masks, there might be an issue, but for most purposes, data will be of a defined format (albeit with variable length milliseconds) such as in your example, and this mask should work perfectly well.
Nice one @takbb , I was actually trying to do this - mix-and-match, somehow I was not successful.
EDIT: Ahh, I see why “the important trick is that the optional masks MUST be specified in descending order of length”. That’s why it did not work for me. The first reaction, at least for me, was to do [S] first, cause that was the first case I saw.
Thank you so much. Its actually saves additional nodes.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.