How do I compare times to find the closest to time in a column?

[Julia1231]

Hi everyone,

From dbxquery, I retrieve this table:

id	start_time1	end_time1	start_time2	end_time2
1234	13/09/2022 21:46:43.0	16/09/2022 12:10:35.414809	15/09/2022 21:46:32.0	16/09/2022 09:27:41.0
1234	13/09/2022 21:46:43.0	16/09/2022 12:10:35.414809	14/09/2022 24:52:03.0	15/09/2022 10:15:56.0
1234	13/09/2022 21:46:43.0	16/09/2022 12:10:35.414809	15/09/2022 10:30:14.0	15/09/2022 10:47:26.0

I want to find the start_time2 that closest to the start_time1, means the 2nd line.

How can I do please?

Thanks,

Julia

[gcusello]

Hi @Julia1231,

if in each event, you have all the fields, you have to run something like this:

<your-search>
| eval diff=strptime(start_time2,"%Y-%m-%dT%H:%M:%S")-strptime(end_time1,"%Y-%m-%dT%H:%M:%S")
| stats values(start_time1) AS start_time1 values(end_time1) AS end_time1 values(start_time2) AS start_time2 values(end_time2) AS end_time2 min(diff) AS min_diff BY id	
| sort -min_diff
| head 1

Ciao.

<your-search>
| eval diff=strptime(start_time2,"%Y-%m-%dT%H:%M:%S")-strptime(end_time1,"%Y-%m-%dT%H:%M:%S")
| stats values(start_time1) AS start_time1 values(end_time1) AS end_time1 values(start_time2) AS start_time2 values(end_time2) AS end_time2 min(diff) AS min_diff BY id	
| sort min_diff
| head 1

or

<your-search>
| eval diff=strptime(start_time2,"%Y-%m-%dT%H:%M:%S")-strptime(end_time1,"%Y-%m-%dT%H:%M:%S")
| sort diff
| head 1

Ciao.

Giuseppe