datetime and timespan
How Berserk holds points in time and durations, the range that follows, and how it differs from Microsoft's KQL
A datetime is a point in time, in UTC. A timespan is a duration, which may be negative.
Berserk holds both as a signed 64-bit count of nanoseconds: a datetime counts from
1970-01-01T00:00:00Z, a timespan counts the length of the duration.
Range
64 bits of nanoseconds reach about 292 years either way, so:
| Type | Smallest | Largest |
|---|---|---|
datetime | 1677-09-21T00:12:43.145224192Z | 2262-04-11T23:47:16.854775807Z |
timespan | about -106,751 days | about 106,751 days |
That covers any timestamp telemetry carries, to the nanosecond.
Outside it, there is no value to give:
- A literal outside the range is an error that names the range:
datetime(2300-01-01)and200000ddo not compile. - A value from your data outside the range is null, with a warning. The query goes on,
and the result says how many values were null and why. That applies to
todatetimeof a string such as"2300-01-01",unixtime_seconds_todatetimeand its siblings,datetime_add,ago, andtodatetimeof a number. - A string that is not a date at all is null without a warning, as in Microsoft's KQL:
todatetime("not a date").
Adding a timespan that takes a datetime past the range is not caught yet, so keep such
arithmetic inside the range.
Compared to Microsoft's KQL
Microsoft's KQL holds both types as 100-nanosecond ticks, and a datetime counts its ticks
from 0001-01-01. That gives it a wider range, from the year 1 to 9999, at 100-nanosecond
precision rather than one. Most queries never notice. Where they do:
| Expression | Microsoft's KQL | Berserk |
|---|---|---|
datetime(2300-01-01) | the date | an error naming the range |
todatetime("2300-01-01") | the date | null, with a warning |
tolong(datetime(2024-01-01)) | 638396640000000000, ticks since 0001-01-01 | the same |
tolong(1d) | 864000000000, ticks | the same |
todatetime(5) | 0001-01-01T00:00:00.0000005Z, 5 ticks after 0001-01-01 | null, with a warning |
totimespan(datetime(2024-01-01)) | 738885.00:00:00, the time since 0001-01-01 | null, with a warning |
todatetime(1h) | 0001-01-01T01:00:00Z | null, with a warning |
totimespan(true), todatetime(true) | one tick | null, with a warning |
A number given to todatetime and tolong of a datetime count ticks from 0001-01-01, as in
Microsoft's KQL, so within Berserk's range those values mean the same in both. What Berserk will not do is
pretend a datetime near 0001-01-01, or a timespan of more than 292 years, exists: those
conversions are null, and the warning says what to write instead.
Converting honestly
To get a number or a duration out of a datetime, say where you count from:
T | extend since_1970 = timestamp - datetime(1970-01-01) // a timespan
T | extend unix_seconds = datetime_diff('second', timestamp, datetime(1970-01-01))
T | extend ticks = tolong(timestamp) // Microsoft's ticksAnd the other way:
T | extend at = datetime(1970-01-01) + offset // offset: a timespan
T | extend at = unixtime_seconds_todatetime(epoch_seconds)unixtime_milliseconds_todatetime, unixtime_microseconds_todatetime and
unixtime_nanoseconds_todatetime take the other common epoch units.