CODEWITHSUNDEEP
azureazure-data-explorerkql
Sean Calvert
Sean Calvert

Reputation: 11

KQL join two tables with different TimeStamp

I´m working with KQL and trying to join two tables on a timestamp field. The problem is that they have different values when it comes to seconds.

The table "TableToJoin" will ingest record every minute (so seconds are 00) and the MeasureTime column I made will have different seconds depending on when I hit the run button (knowing it will starting counting 36h from now)

Do you know a method I could use to fix this?

I paste my code below:

range MeasureTime from ago(36h) to now() step(10m) 
| join kind=rightouter 
(TableToJoin| where TagName == 'TagName') on $left.MeasureTime == $right.Timestamp | take 10

TableToJoin TimeStamp:

2021-11-01T14:09:00Z
2021-11-01T14:08:00Z
2021-11-01T14:06:00Z
2021-11-01T14:05:00Z
2021-11-01T14:04:00Z
2021-11-01T14:03:00Z
2021-11-01T14:02:00Z
2021-11-01T14:01:00Z
2021-11-01T14:00:00Z

MeasureTime TimeStamp:

2021-11-01T13:59:20.5230363Z
2021-11-01T14:00:20.5230363Z
2021-11-01T14:01:20.5230363Z
2021-11-01T14:02:20.5230363Z
2021-11-01T14:03:20.5230363Z
2021-11-01T14:04:20.5230363Z
2021-11-01T14:05:20.5230363Z
2021-11-01T14:06:20.5230363Z

Thanks in advance

Upvotes: 0

Views: 1061

Answers (1)

Avnera
Avnera

Reputation: 7618

You can use the bin() function to "round" the timestamp. For example:

range MeasureTime from ago(36h) to now() step(10m) 
| extend MeasureTime = bin(MeasureTime, 10s)
| join kind=rightouter (
    TableToJoin| where TagName == 'TagName'
) on $left.MeasureTime == $right.Timestamp | take 10

Upvotes: 2

Related Questions