How do I round a datetime to the nearest minute in KDB?

user_222151 0
Viewed 130

I have an array of timestamps of the form:

2022.05.23T14:31:04.222

I'm trying to round them to the nearest date-minute, such that the output would be 2022.05.23T14:31:00.000.

I've various casts (e.g. `minute$t), and also 00:01:00 xbar t, but none of these worked.

How should it be done?

kdb
3 Answers

A few suggestions:

  1. Don't use datetime datatype (aka .z.Z, aka type 15, aka "z"$) use timestamp datatype (aka .z.P, aka type 12, aka "p"$). The former is underpinned by a float (not good for bar'ing) while the latter is underpinned by long.

  2. Your 00:01:00 xbar t approach almost works, you just need to use timestamps for t and datespan (aka .z.N, aka type 16, aka "n"$) for the xbar:

q)t:"p"$2022.05.23T14:31:04.222 2022.05.23T14:32:04.222;
q)0D00:01:00 xbar t
2022.05.23D14:31:00.000000000 2022.05.23D14:32:00.000000000
user_38956970

Add 30 seconds; cast to year and minute; sum.

q)t
2022.05.23T14:31:04.222

q)f:sum "du"$ ("v"$30)+  / composition

q)f t
2022.05.23D14:31:00.000000000
q)f t+"v"$40
2022.05.23D14:32:00.000000000
user_35401940

Here's one method (this will only round down though):

q){`datetime$(`date$x)+`minute$x}2022.05.23T14:31:04.222
2022.05.23T14:31:00.000

This should round up & down:

q){"z"$("d"$x)+{$[30=`ss$x;00:00:30+;]x}30 xbar"v"$x}2022.05.23T14:31:04.222
2022.05.23T14:31:00.000
q){"z"$("d"$x)+{$[30=`ss$x;00:00:30+;]x}30 xbar"v"$x}2022.05.23T14:31:34.222
2022.05.23T14:32:00.000
user_93644150
Related