Date Time Calculation help

Hello, I have a date time column that looks like this: “05.Jun.2020 10:58:35.893”. I want to filter my data to only return the lines that were in the previous 24 hour period. So If today is June 10th, I want only the lines from June 9th or 09.Jun.2020 00:00:00.000 to 09.Jun.2020 23:59:59.999.

I’ve tried to use a few different DateTime nodes (like Date&Time Difference) and I get this error when I try to configure:

The dialog cannot be opened for the following reason: No column in spec compatible to “LocalDateValue” “LocalTimeValue” “LocalDateTimeValue” or “ZoneDateTimeValue”

Can someone please point me to correct way to handle this?

Thank you

Hi,

First you have to use “String to Date&Time” node (the date format is dd.MMM.yyyy HH:mm:ss.SSS ) and then the “Extract date&Time Fields” where you can extract month, day, hour,…

Hope this helps

Kind regards
Andrej

4 Likes

Thank you for your reply but here’s the new problem I’m seeing, when I try to do the “String to Date&Time” it doesn’t recognize the column (“TicketCloseTime” )I’m trying to convert as text . See below.

At the same time, when I try to use the “Modify Time” node, or any other node to modify a date/time column, it doesn’t recognize it as a Date/Time column. See below, It doesn’t show any Date/Time field available to edit…??? Help! What am I missing here?

The node is named “String to Date & Time” because it takes a column of data type String and creates cells in a colum of data type something-involving-date-and-or-time.

This is why the usable columns listed in that screen shot all have a S in a white box prefixing them, and the reason why your TicketCloseTime is not usable.

It sounds like you want a node that does the opposite conversion? If so:

Hi,
The icon near the “TicketCloseTime” is a calendar with a clock that means taht is alredy recognized as Date&Time format .This is the reason why “String to Date&Time” it doesn’t recognize it.

You can already use “Extract date&Time Fields” to extract the field you want.

Thank you everyone for your replies. Here’s the conundrum- the “TicketCloseTime” looks like a date and time column but when I try any node that manipulates date/time including the “Extract date&Time Fields” as suggested by @andrejz I get this error below when I try to open the “Configure”:
image

Can anyone figure out what’s happening?

Thank you,

Mona

Perhaps it’s a legacy date & time - try converting it prior to the extract node?

1 Like

Hi,

Look at this simple example and compare your settings Date_Time.knwf (8.6 KB)

Can you upload your workflow with some data?

May be you need something like this Date_Time.knwf (24.1 KB)

Hi there @MJRIOUX,

as @quaeler pointed out you have a legacy Date&Time format. Convert it to new Date&Time format and you’ll be able to use above mentioned nodes as expected.

Additionally take a look at this blog post: https://www.knime.com/blog/the-new-date-time-integration

How did you end up with legacy format?

Br,
Ivan

1 Like

Thank you @quaeler ! that worked perfectly. Now I have to figure out the best node to be able to filter to now minus one day. In other words I need to filter to just yesterday’s date. Any suggestions?

Mona

Thank you, it was a legacy date issue. now solved.

Mona

Hi @MJRIOUX,

See this topic:

Br,
Ivan

1 Like

Thank you everyone! I figured it out! I used the “Date&Time-based Row Filter” and it works perfectly.

1 Like

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