KQL bin on timestamp yields different results than on unix timestamp

Question

Consider the following table:

let fooTable = datatable(ts: long) [
    1655139949044,
    1655053767530,
];

It has two unix timestamp values in milliseconds, and the dates are:

2022-06-12T17:09:27.53Z
2022-06-13T17:05:49.044Z

I want to COUNT() group by the date rounded by week.

One query uses bin on timestamp field,

let fooTable = datatable(ts: long) [
    1655139949044,
    1655053767530,
];
let bucket_ts = 1m * 10080;
fooTable | summarize count() by bin(unixtime_milliseconds_todatetime(ts), bucket_ts)

And the second query uses bin on Long value.

let fooTable = datatable(ts: long) [
    1655139949044,
    1655053767530,
];
let bucket_long = 60000 * 10080;
fooTable | summarize count() by bin(ts, bucket_long) | extend ts=unixtime_milliseconds_todatetime(ts)

I'm trying to understand why they yield different results - I expected results to be the same (for example, former yields 2 rows, and the latter 1 (as I expected)).

It seems like when bin on date types - it groups it by constant time buckets, but on long types it simply floors it to the previous week (based on the relative time).

I want to bin on datetime types, to behave like the Long example. How can I do that still using the datetime type?

I guess the easy answer is to convert the datetime to Long, but this solution seems quite messy to me.

user_6336479 · Answer

unixtime starts with 1970-01-01

print unixtime_milliseconds_todatetime(0);

print_0
1970-01-01T00:00:00Z

datetime starts with 0001-01-01

print todatetime(0);

print_0
0001-01-01T00:00:00Z

Different reference points, different results.

Use bin_at() with 1970-01-01 to match the datetime bin to the long bin

let fooTable = datatable(ts: long) [
    1655139949044,
    1655053767530,
];
let bucket_ts = 1m * 10080;
fooTable | summarize count() by bin_at(unixtime_milliseconds_todatetime(ts), bucket_ts, datetime(1970-01-01T00:00:00Z))

Column1	count_
2022-06-09T00:00:00Z	2

Fiddle

KQL bin on timestamp yields different results than on unix timestamp

1 Answers