Date&Time: Split Date and Time apart


this was presumably once available via the Mask Date/Time node but that went legacy. The Modify Date node is unable to cope with Date & Time fields to i.e. strip off the time and only keep the date. I am able to extract the desired date via RegEx or String manipulation but that is unnecessarily complex and requires data conversion afterwards.

Therefore, I’d like to suggest to create a node that allows to split individual data like Date and Time apart. Maybe the Extract Date&Time Fields node only needs an option to create concatenated data instead of splitting it into individual columns?


Hi @mwiegand , I agree that a one step extraction node would be a nice idea. Currently I use Date&Time to String followed by String to Date&Time to perform such conversions without the need for complicated string handling, but it is of course two nodes instead of one

Considering that there are many different date&time formats, there is a great chance of failure when using rather “static” strings compared to logically structured date&time data.

Well…I’m talking about something that is currently held as one of the Date/DateTime datatypes. Those datatypes don’t have a format as such.

So when converting it to a date string using Date&Time to String, you control the format of that conversion by specifying what format you want the output string to be, so you can absolutely guarantee the format of the string that is produced. So in the subsequent String to Date&Time you know the format of the reverse conversion. I cannot think of a scenario where this wouldn’t work (but I haven’t had my coffee yet so there’s always a possibility… :wink: )

I agree though, that having to use an intermediate String step is not ideal. It’s just the best solution I have at the moment…

[Edit: When attempting the conversion from a known Date-Time String, to just a Time, the Date&Time to String component appears to give an error which is off-putting:

However, provided you have defined the format correctly, it works. In trying it just now I nearly came to the conclusion that I was wrong, and that the conversion doesn’t work after all, but it seems we have to ignore the “red alert” …

What I had in mind about different date time formats is that if the source differs. Classic scenario is US “yyyy-mm-dd” with 12 hours (AM/PM) vs. the “rest” of the world “dd-mm-yyyy” and 24 hours. Or even simpler, if servers are not correctly setup so not all use the same format.

When all the data, thinking about IOT here, is gathered in the same column, ensuring consistency is almost doomed to fail. Which actually might imply the necessity for a node that analyzes the string date&time input and automatically suggest a corresponding format in order to, group wise, process the data. Do you also think this “Date&Time Format Analyzer” might be another feature worth to suggest?

Yes, when receiving a date as a string, then what you say is correct that you will need to know the format. But that is going to be an issue no matter what nodes you have.

But once we have got the date held internally as a Date, you are in full control, and any conversions can be made very easily because you no longer need to worry about regional formats…

When a date is actually held as a Date datatype (e.g. LocalDate), there is no such thing as “date format”. It doesn’t matter which part of the world you are in, what your system locale or region is set to, as internally a date datatype is held the same way.

Dates do not have any format of their own, which is often why on the forum people mistakenly ask “how can I change to format of a date”, when what they mean is “how can I change the way the system visually displays the date”. Its a subtle but important distinction.

It is only when you attempt to visually represent a date, that a decision about the display format is made. But that is nothing to do with the date.

So if you have a column LocalDate and you elect to convert it to a String using Date&Time to String, you can specify whatever format you wish e.g. yyyyMMddHHmmss, and then when you use String to Date&Time, you tell it the same format yyyyMMddHHmmss and tell it you just want the time part, and it will work no matter what your region or other settings are.

Regarding a “date&time format analyzer”, there is only so far that can go. If dates have a day number exceeding 12, and a 2 digit year exceeds 31, it can kind of work,

e.g. “01-13-32” is clearly in mm-dd-yy format, and 14-03-2023 is clearly dd-mm-yyyy format
but “01-02-03” is ambiguous, as is 12-03-2023, and so such an analyzer can still make wrong guesses, albeit this improves with a larger data set.

I think that the existing date/time nodes could be considerably improved in the way they parse dates though, as at the moment they are far too rigid. Java allows dates to be parsed with less strict rules, if the options are set (case-insensitive for example: who cares if we write “Jan” or “JAN” in our date string or whether we use AM/PM or am/pm. It is “obvious” what we mean yet it causes problems all the time!)

I did some work on producing a simple date parsing component, which can make a pretty good stab at interpreting some date strings in very loose formats, so it demonstrates that better parsing capabilities are achievable relatively easily

Here though, because of the potential for ambiguity as in the examples I gave above, it is necessary for the component to be given a hint (it is told which ordering to expect D-M-Y, Y-M-D, Y-D-M or M-D-Y) and whether the time portion (if present) is after the date. (There are a few things yet to be implemented. e.g. that bit about the position of the time - it currently doesn’t have the code for handling the time appearing before the date. I haven’t got round to implementing that, but it makes for a demonstration, and does a job, if I need it)

Sorry I’m getting way off your original topic!

Ha ha, never mind flexing your “Knime-muscles” once a while. I am myself working on a test workflow to better display the possible implications, shortfalls, workarounds and necessities in regards to date&time data. Which brings us back to the original topic :wink:

Also, thanks for spending so much time and efforts conversing about this subject @takbb!

1 Like