Extracting date from string

[summer_le]

Hi there,

I have a date/time string that I want to convert to just date.

I have a logic in my head but it seems very simply and I wanted to know if anyone else has a more elegant way of doing it:

Format: 20221202132808

I was going to convert the D integer to string then use a substr (string manipulation) to extract the year-mth-day and the insert delimiters to separate the y m d and then use date modify to convert it date.

But this seems like such a long winded way of doing it.

Does anyone have a better method?

Thank you.

[takbb]

Hi @summer_le ,

If your data is a String, you can use the String to Date&Time node for this.

(On re-reading your question, are you saying your current “date string” is actually held as a Double…?
If so, then first use the Round Double node to convert it to a String

In the String to Date&Time node…

Specify the date format as yyyyMMddHHmmss which is the format of your data, and then set the “New Type” to “Date”

If you then want your date back as a string in a specific format, simply attach a Date&Time to String node and give it the required output format
e.g.

Convert Date held as Double to date or date string.knwf (11.6 KB)

[summer_le]

Hi Takbb,

This worked perfectly, thank you.

Upon review of my work, I realised that I do need the time as well, down to the second and I wanted to convert it to something that looks more consumable than: 20220606185460

The only problem is converting this to Date&Time gives an error because the second is 60 and the node can only cater for 0 - 59 for SecondofMinute.

How can I account for this and be able to display: 20220606185460 in a more presentable way?

Thank you.

[takbb]

Hi @summer_le , do you need the seconds or would a time down to the minute be sufficient?

What time does 185460 represent?

Is it 18:54:59 or 18:55:00?

[summer_le]

Hi Takbb,

That’s a good question.

I think I will need it down to the second but in saying that I just checked the dataset (there’s millions of records so I didn’t notice how many duplicates there were) and looks like the best way to represent 185460 is as 185500.

My reasoning for this is because the next record after 20220606185460 is actually 20220606185501

So looks like for some reason, instead of 185460 being 185500, the system is recording it as 185460.

But ultimately, having it down to the second will be critical, but I won’t bore you with the details why.

Thank you.

[takbb]

Hi @summer_le , thanks for the update. Can i just check then, does that mean there are currently no 00 seconds in the data, so the range is currently showing as 01 to 60, but the 60 should be 00 of the following minute?

If that is the case then you can perform an initial transformation on your data to correct it:

If the datetime number modulo 60 is zero, then add 40 to it.

But this would need to be adapted a little if 00 is also present.(ie also check that the number modulo 100 is not zero)

[summer_le]

Hi Takbb,

I just had a quick check and found a bunch of records with 00 for seconds.

So looks like the range for seconds is 00 - 60, which is confusing.

Kind regards,
Sum

[takbb]

Hi @summer_le , well I guess the correct thing to do in this situation is attempt to find the source of the error, and then either it can be corrected at source, or if that is not possible, perform the correction yourself.

I’ll make an assumption that some process is maybe adding a duration to a time, or something similar and incorrectly handling the “60” boundary. In this case I’ll assume that values of 00 to 59 are correct, and that 60 represents 00 of the following minute.

The attached workflow uses a Math Formula node to handle the correction.

if(mod($column1$,60 )==0,
	if(mod($column1$,100 )!=0,
		$column1$+40,
		$column1$),
	$column1$)

This says “if the number is divisible exactly by 60, but is not divisible exactly by 100, then add 40 to it, otherwise leave it alone”

Obviously you’ll need to decide for yourself if this is a valid assumption to make, or whether you are able to track down the source of the original “data anomaly” to check if this is a valid approach.

Convert Date held as Double to date or date string with seconds correction.knwf (14.1 KB)

[summer_le]

Hi Takbb,

That’s a great suggestion, thank you.

I think I’ll make an educated assumption that 60 is meant to be 00 of the following second and treat it as such.

Thank you for this,

Sum.

[summer_le]

Hi @takbb and anyone else interested, the solution above is good but there is the issue of any times ending in 120 will face an issue as 120 is also divisible by 60, which caused an error so remember to cater for that as well.

[takbb]

Hi @summer_le , ah yes of course! Glad you got it working.

Thanks for the update and marking my answer as the solution.