Count Rows for timestamp between start and stop date

KNIME Analytics Platform
#1

Hi there,

I have a table with a start date and a stop date. I would like to count for every full hour from minimal start date to maximal stop date, how many occurences are for start_date_normalized_to_full_hour <= full_hour_variable <= stop_date.

|rowid|start_date|stop_date|
|1|01.01.2018 06:13|01.01.2018 08:18|
|2|01.01.2018 07:13|01.01.2018 09:18|
|3|01.01.2018 08:13|01.01.2018 10:18|
|4|01.01.2018 09:13|01.01.2018 11:18|
|5|01.01.2018 06:13|01.01.2018 12:18|
|6|01.01.2018 10:13|01.01.2018 13:18|

with the following result

|date|occurence|
|01.01.2018 06:00|2|
|01.01.2018 07:00|3|
|01.01.2018 08:00|4|
|01.01.2018 09:00|4|
|01.01.2018 10:00|4|
|01.01.2018 11:00|3|

Many thanks

#2

Hi @brammen,

you can use the Java Snippet node to truncate the dates to full hours:

import java.time.temporal.ChronoUnit;

out_truncatedStartDate = c_start_date.truncatedTo(ChronoUnit.HOURS);
out_truncatedStopDate = c_stop_date.truncatedTo(ChronoUnit.HOURS);

Also, the Create Date&Time Range node generates individual time points between start_date and end_date and the *GroupBy` can be used to count occurrences.

Take a look at this workflow: 11207.knwf (19.3 KB)

Best,
Stefan

#3

Thank you!

#4

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