Integer to date

Hi Knimers

Simple doubt...How can I transform my integer column (YYYYMMDD)  to a date format?

Ps: I transformed to a String and tried to use String to Date but it's not working.

Tks!

use a number to string node to convert the integer to a string

use a string to date/time node with the Date format configuration yyyyMMdd to convert the string to a date

 

2 Likes

Works!
My bad, was simple.

Tks!

Hi there,

I am new to KNIME and have similiar question , would like to know how to convert serial number to Date format. For example :

this serial no 42370.000081019  actually is 1-1-2016.

thank you

 

 

 

 

Hi Manish!

Can you share some data?

I'm not sure yet how we can tackle this.

Cheers

 

use a Date/Time Shift node

the serial date column has to be an integer, if you have fractions of a day, shift the day part first, then add the time part separately.

Have a column of serial no as tne input to the Date/Time Shift node. In the node, configure it to "Use shift value from column", select granularity of shift as Day, and in the Date reference section, choose "Use fixed date", and in the example you have, check the Use date and set the year as 1989, the month as 12, and day as 30.

This will return 1 Jan 2016 for the serial number 42370

 

 

1 Like

Hi Davide,

Thank you very. I have been able to get the desired output after applying your inputs.

 

 

Hello knimers,

I also have string value of “yyyyMMdd” and like to convert it to a date…
But there is a message called "No suitable format found…
What should I do ??
The content of the first cell is for example 20130415 is already a string and should fit to the date format yyyy-MM-dd


Can you help me?
BR
Bastian

Like one of the previous replies said, just type yyyyMMdd for the Date format.

Or click the button to guess at a format.

3 Likes

Hi @8bastian8, you aren’t the first to get caught by this not realising that you can just overtype the format on that drop down list, and I suspect you won’t be the last… :wink:

3 Likes

Yes, such an intuitive solution! :sweat_smile:

1 Like

Hi, @elsamuel. You have already helped me a few months ago, with my doubts in my research with COVID data. Now, my doubt is on another point.
I have grouped (by months and years) all COVID cases, hospitalizations, and deaths in separate rows (one row by month). I have also calculated (as new features) respective rates (for incidence (= number of cases/10,000 inhabitants); hospitalization rates (= % of positive cases with hospitalization); and lethality rates (= % of positive cases with the death of patients).
And also got one column with the year (type Integer) and another with the month number (type integer). I have saved this table as a CSV file, as follows:
Brazil-RS-Covid-Data-With-Rates.txt (316.1 KB)
Now I need to combine (or concatenate, I don’t know where was my mistake) these two columns (year and month), in the format YYYY-MM (and in Date&Time type). Additionally, I need MM (for months) with two digits, but Knime has given me a table with one digit for months from January to September (1 to 9).
I tried some alternatives (e.g., Column Combiner; Columns Merger), but didn’t get the desired format (yyyy-mm)
Can you help me? Thank you (again).
B.R.,
Rogério.

I think this would’ve been better as its own thread.

As far as I’m aware, the KNIME date format requires at minimum, a year, a month, and a date. So, you won’t be able to convert a YYYY-MM string to a Date&Time format.

Hi, @elsamuel. Thank you for your prompt answer.
Then, I think it could work out for me, as well, if the resulting column kept its string type, provided I would be able to order the rows in the table similarly by numerical order → What I need is to order those data along time (even they are not in a Date&Time format).
My goal with this serialization is to plot those serial data along time (thus I need to sort those strings (representing dates of diagnosis) in an ascending alphabetical/numerical order), after concatenating the two original columns (with the year and with the month) into one with the format: yyyy-MM (with two digits for every month from 1 to 9 (January to September))). I tried (unsuccessfully) to use Column Expressions node but got no effective syntax for doing so. Do you know how this could be done?
My second doubt relies on an appropriate answer to the former: I am currently working on those data (identified by the municipality and those calculated three rates). And I need to cluster them according to their rates, to investigate if different cities could (or not) be gathered by the similarity of Covid’s dissemination (as measured by incidence) or disease severity (as measured by hospitalization rate or by lethality rate). I suppose I would have to make one separate clustering branch of my workflow for each rate. Can you suggest to me how to group (I mean, to cluster) those cities by rate?
Thank you once again.
B.R.,
Rogério.

Can you help me on choosing a few clustering algorithms, to compare and score which ones (of the tested algorithms) could perform better than the others?

Hi again, @elsamuel,

I worked on your former answer, and I already got to concatenate those two columns (with year and month), with the desired two digits for months, using Column expressions. Thank you.
My second doubt relies on this fact: I am currently working on those data (identified by the municipality and those calculated three rates). And I need to cluster them according to their rates, to investigate if different cities could (or not) be gathered by the similarity of Covid’s dissemination (as measured by incidence) or disease severity (as measured by hospitalization rate or by lethality rate). I suppose I would have to make one separate clustering branch of my workflow for each rate. Would you suggest to me how to group (I mean, how to cluster) those cities by rate? Can you help me in choosing a few clustering algorithms, to compare and score which ones (of the tested algorithms) could perform better than the others? I imagine I should use one of the Scorer nodes to evaluate their performances (but I don’t know yet which one to use).

My goal with this serialization is to plot those serial data along time, even though some cities change from one cluster to another along time. But I still need to plot those rates for each cluster along time (i.e., my 16 months of research).

1 Like