# Round up time values

Hello all,
I want to round the column with time data type to the closest 15 minutes of an hour to the bigger side(please see the table).

Time Rounded time column
5:03:15 5:15:00
5:09:12 5:15:00
5:21:33 5:30:00
5:31:03 5:45:00
5:59:09 6:00:00

In excel I did that with the help of =CEILING(A2 of “Time column”, TIME(0,15,0))

Is it possible to do in Knime?

Thank you all.

My final goal is to do clusters and do group by:
5:00:00
5:15:00
5:30:00
5:45:00
6:00:00
6:15:00
etc

hi @IMR2KA
my nodes approach.

i hope it meets your expectations.
rgds

1 Like

Hi @IMR2KA ,

This can also be done using some mathematical calculations, based on the number of seconds that the time is since midnight:

The Date&Time Difference node can return the number of seconds since midnight:

Once you know the seconds since midnight represented here as \$calctime\$, then the seconds since midnight for the next quarter hour (rounding up if the time is not on the quarter hour) can be found using the formula below:

`floor((abs(\$calctime\$) + 899) / 900) * 900`

In the above…
900 = seconds in a quarter hour
899 = seconds in a quarter hour minus 1 second

This adds 899 seconds, and then rounds down to the previous quarter hour, which has the overall effect of rounding up, unless the time is already on the quarter hour

After this, the Unix Timestamp to Date&Time node can turn seconds since midnight back into a time.

3 Likes

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