String to Date&Time

Dear colleagues,

I’m working on the workflow “space missions 1957-2020” analysis in Knime, so while doing it I touched the problem with the column “Datum” transformation into date & time format. I need to have “Date” column and “Time” column separately. I suggest it happened due to that fact that this column contains the day of the week.
So I cannot split it into date and time accordingly. Please, help me to solve this problem. I’ve already used the node “string to Date&Time”, it does not work, the node “date &time split”, it does not work too. So, maybe there are some nodes that I’ve not knew yet. In any case, thanks in advance. Here below I attached the file with the initial format to be transformed.

Hi @Felis90 , you can use two separate String to Date&Time nodes, each configured to append columns, using the mask

EEE MMM dd, yyyy HH:mm z

as follows:

** Append Date column **

Append Time column:

If your data contains September data as “Sep”, use the locale en-US or another suitable locale of your choosing. If it contains September data as “Sept”, use the locale en-GB.

Provided that all your date times are the same timezone (eg UTC), this should be sufficient. However, standalone Date and Time columns do not contain the timezone information of your original data and so the dates and times recorded will not necessarily be correct if you have data from different timezones. (This is the reason for the red warning messages).

e.g. 03:13 UTC and 03:13 CET would both return the time as “03:13”, resulting in potential information loss, as you can see here:

2 Likes

Thanks for your reply) but I have no such date format as you, starting with EEE… what I can do in this case?( the other are not suitable…

The dropdown list contains some common formats but you can just overtype with other formats as required. You are definitely not the first person to not realise this, as it isn’t immediately obvious. :wink:

btw, welcome to the KNIME forum!

You’re absolutely right, I’m not the first, but))) I wrote it, as you said, and push “apply” button, then OK, then “Execute”, then nothing happened, so it looks like flashing screen when I’m trying to execute … and the table below not opening… the “traffic light” is yellow…

Remark: I even tried replacing z to Z (capitalized) due to the fact that there is UTC… and again nothing(



Hi @Felis90 , the error message appears to be saying that on Row106, your data is not the same format and doesn’t contain the time.

You could try the following mask

EEE MMM dd, yyyy[ HH:mm z]

which should make the time part optional and also untick the “fail on error” which would then mean you get missing value where it cannot interpret the data using the given date time mask.

You’ll need to decide what you want to do if there is no time.

4 Likes

Perfect!!! @takbb You are Perfect!!! Thanks a lot!!! Million thanks!!! It really worked!!! For me in terms of Analysis the Time is not so important as Date!!! So it is really brilliant job!!!
And the last question for tonight:
I’m the beginner of doing all processes in Knime, as you already understand, and now just want to make it clear for myself: is it possible to ask you directly somehow here for helping me in issues occurred, if any, or it is just possible in Q&A chat like this one, while generating topic, and somebody, who see my problem can answer me how to solve it?

Many thanks in advance!!!

Hi @Felis90 , I’m pleased that it worked for you and welcome once again to the world of KNIME.

To answer your question about asking questions… it is best to ask here on the forum generally as you have done so, rather than try to ask a new question of a specific person.

The main reasons for this are:

  • although there are forum regulars here, none of us guarantee to be around at any given time as we simply volunteer our time when we are able and might not be back for several days, weeks or longer
  • none of us knows everything… So just because I have a solution for one problem, doesn’t mean I’m the best person to answer a different question
  • there is often more than one potential solution to a problem and different people have different ideas. If you ask one person, you may not always get the best solution whereas asking more generally you’re more likely to get a variety of solutions from which you can learn. I’m learning all the time from other suggestions and ideas from other forum members.
  • finally, and this is more subjective, here on the forum, if somebody tags my name on a brand new question that I haven’t been involved with previously just because they think I might know the answer, or because nobody else has answered, even though it can be flattering that they think I might know, it can also feel a little intrusive or rude… If I see a general question posed and I have a possible solution, and, importantly, have time to respond then I will generally do so, and I think other people probably feel much the same way. But I can be put off a little if somebody constantly tags me on their new questions. Stange, maybe, but true! :wink:

I hope that helps, and I have one favour to ask…

Thanks!

3 Likes

@takbb many thanks and regards!

There is another one issue on this topic. After doing this procedure in the statistics it can be seen the following: in 126 Datum (date) / Datum (time) cases the values are missing. So, in parallel I do the same job, I use “string to date&time” node, then put the format [EEE MMM dd, yyyy] as it was requested by 126 rows… now I try to “join” somehow the first part of my job with the second part to have one column with only the Date. And it does not happen, probably it relates with incorrect usage of the node. They exist as two separate columns in parallel…. So, maybe you know which one node I can use to join them?




And then another one question, not directly on this topic but related with similar one: I have “Rocket” column with price in fact, and when I tried to transform it from string to number format, the statistics shows that I have more than 3000 missing values w.r.t. total number of rows (4324) it is too much… what I can do in this case, because price, contained in “Rocket” column is very important for analysis…

@takbb, I have the update: I have joined these above mentioned columns via consistent series of nodes: string to date & time (transform first part of date format) then string to date&time (transform time), then string to date &time (transform the second part of date format), then I used column merger node which merge the first part transformed date with the second one, so now I have separate column called Date 3 with all dates in one format!

But the second question is still open) kindly ask you to see it, I hope you have a solution))) best regards)

1 Like

Hi @Felis90 , Glad to hear you found the column merger node!

I think it would be better to ask the new question re String to Number conversion as a new topic with a new title for better visibility. When you post the new question, please include examples of the data that you are trying to convert as I suspect maybe it contains commas or currency symbols or other punctuation or white space which would require additional conversion steps.

Ideally uploading a small file containing some sample data would make it easier for people to suggest (and test) possible solutions. I will also look at the new question and assist if I am able.

2 Likes

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