How to Convert Timestamp from 1 min to 5 min

[vaidyarm]

we have the number of rows divided by 1 min each

“2023-04-01 12:00:00 UTC”
“2023-04-01 12:01:00 UTC”
“2023-04-01 12:02:00 UTC”
“2023-04-01 12:03:00 UTC”
“2023-04-01 12:04:00 UTC”
“2023-04-01 12:05:00 UTC”
“2023-04-01 12:06:00 UTC”
“2023-04-01 12:07:00 UTC”
“2023-04-01 12:08:00 UTC”
“2023-04-01 12:09:00 UTC”
“2023-04-01 12:10:00 UTC”

we need to group them over a period of 5 min instead of 1

“2023-04-01 12:05:00 UTC”
“2023-04-01 12:10:00 UTC”

0-5 min > 5 min
6-10 min > 10 min

Can anyone help me with it? there are a huge number of timestamps, not limited above example, once we have this, we will be able to aggregate data for every 5 mins

[del]

There are probably a dozen or more ways to do this, but not knowing your full requirements, I’ll just give a couple of expression language examples that I worked up just to get the minimal output for an example. My output is just integers that you could plug back into whatever format or object structure you need for downstream aggregation.

The cleanest one that I like so far (assuming the math works for your use case) uses an arrow function with String replace() regex to parse the hour for the input:

(x => x + (5 - ( x % 5)))(parseInt($inputDate.replace(/^[^ ]+ \d+:(\d{2}):.+$/ ,"$1")))

But, if that math or some variation doesn’t iron out for your needs, then you could use the match operator with the same parsing method in a way similar to this:

match parseInt($inputDate.replace(/^[^ ]+ \d+:(\d{2}):.+$/ ,"$1")) {0..<5 => 5,5..<10=> 10,10..<15 => 15, _ => 'error'}   

The examples convert the hour part into an integer for simpler processing. Obviously, if your input time formats are different, you will need to tweak the parsing to your needs.

Here is the pipeline I tested the ideas with:
Community.17000_v1.slp (3.8 KB)

I hope this helps.

[Abhishek_Soni37]

Hi @vaidyarm

If you just want to filter the records where time is multiple of 5, then may be you can use below expression the filter snap:

Date.parse($inputDate.substring(0,19)).getMinutes() != 0 && Date.parse($inputDate.substring(0,19)).getMinutes() % 5 == 0

Output will look like this:

I hope this helps 🙂