🌌🏭KQL Reloaded: First Steps (Part II: Working with Dates)

Knowing how to work with dates and manipulate them accordingly is probably one of the important pieces of knowledge in any query language. Fortunately, KQL has many date-related functions that can help developers in the process and the available documentation is detailed enough to support users in learning their use. The current post focuses on the main uses of dates. 

Please note that the scripts consider the main time units. Please check the documentation for functions' call for other time units.

Create dates via make_datetime, now and ago functions:

// creating dates from parts
print datetime1 = make_datetime(2025,1,1)
, datetime2 = make_datetime(2025,1,1,0,0,0)
, datetime3 = make_datetime(2025,1,1,1,2,3)

// creating dates from values
print date1 = datetime(2024-02-01)
, datetime(2024-02-01 00:00:00)
, datetime(2024-02-01 23:59:59)
, datetime(2024-02-01 23:59:59.123)

// now vs todatetime (return the same value)
print datetime1 = now()
, datetime2 = todatetime(now())
, datetime3 = ago(0h)

Extract information from dates via datetime_part, substring and individual functions: getyear, week_of_year, monthofyear, dayofyear, hourofday, dayofmonth, dayofweek, dayofyear, dayofmonth, dayofweek, endofyear, endofweek, endofmonth, endofday, startofyear, startofweek, startofmonth, startofday:

// date parts front date
print now = now()
, year = datetime_part('year', now())
, month = datetime_part('month', now())
, day = datetime_part('day', now())
, hour = datetime_part('hour', now())
, minute = datetime_part('minute', now())
, second = datetime_part('second', now())

// date parts from string 
let t = datetime("2024-12-31 10:35:59");
print year = substring(t, 0, 4) 
, month = substring(t, 5, 2) 
, day = substring(t, 8, 2) 
, hour = substring(t, 11, 2) 
, minute = substring(t, 14, 2) 
, second = substring(t, 17, 2) 

// date parts via functions
print year = getyear(now())
, week = week_of_year(now())//ISO 8601 compliant
, month = monthofyear(now())
, day = dayofyear(now())
, hour = hourofday(now())

// day functions
print year = dayofyear(now())
, month = dayofmonth(now())
, day = dayofweek(now())

// end of time dates
print year = endofyear(now())
, week = endofweek(now())
, month = endofmonth(now())
, day = endofday(now())

// start of time dates
print year = startofyear(now())
, week = startofweek(now())
, month = startofmonth(now())
, day = startofday(now())

//time units
print hours1 = time(1hour)
, minutes1 = time(1min)
, seconds1 = time(1second)
, hours2 = totimespan("0.01:00:00")
, minutes2 = totimespan("0.00:01:00")
, seconds2 = totimespan("0.00:00:01")

Working with dates via datetime_add and datetime_diff functions:

// adding time units
print year = datetime_add('year',1,now())
, month = datetime_add('month',1,now())
, day = datetime_add('day',1,now())
, hour = datetime_add('hour',1,now())
, minutes = datetime_add('minute',1,now())
, seconds =  datetime_add('second',1,now())

// data differences
print years = datetime_diff('year', now(), ago(1h))
, months = datetime_diff('month', now(), ago(1h))
, days = datetime_diff('day', now(), ago(1h))
, hours = datetime_diff('hour', now(), ago(1h))
, minutes = datetime_diff('minute', now(), ago(1h))
, seconds = datetime_diff('second', now(), ago(1h))

Working with time zones via datetime_local_to_utc and datetime_utc_to_local functions:

// local time across time zones
print ParisTime = datetime_local_to_utc(now(),'Europe/Paris')
, LondonTime = datetime_local_to_utc(now(),'Europe/London')

, LondonTime = datetime_local_to_utc(now(),'Europe/Budapest')
, AthensTime = datetime_local_to_utc(now(),'Europe/Athens')

// local time across time zones
print ParisTime = datetime_utc_to_local(now(),'Europe/Paris')
, LondonTime = datetime_utc_to_local(ago(1h),'Europe/London')
, BudapestTime = datetime_utc_to_local(ago(-1h),'Europe/Budapest')
, AthensTime = datetime_utc_to_local(ago(-2h),'Europe/Athens')

Applying different formatting with tostring, format_timespan and format_datetime functions:

// date and time to string
print date1 = tostring(datetime(2024-02-01 00:00:00))
, time1 = tostring(totimespan("0.01:02:03"))
, hours1 = tostring(time(1hour))

// formatting timespans
let t = time("25.10:35:59.123456");
print date1 = format_timespan(t, 'dd.hh:mm:ss:FF')
, date2 = format_timespan(t, 'ddd.h:mm:ss [ffff]')

// formatting dates
let t = datetime("2024-12-31 10:35:59");
print date1 = format_datetime(t,'yyyy-MM-dd [HH:mm:ss]')
, date2 = format_datetime(t,'yy-MM-dd HH:mm:ss')
, date3 = format_datetime(t,'yy-MM-dd [HH:mm:ss tt]')

Considering the above functions, one has a good basis for working with dates. 

Happy coding!

Previous Post <<||>> Next Post