Datetime functions

​

DateTime/ Timespan functions

• We support the ISO 8601 format, which is the standard format for representing dates and times in the Gregorian calendar. Check them out here

​

ago()

Subtracts the given timespan from the current UTC clock time.

​

Arguments

• Interval to subtract from the current UTC clock time

​

Returns

now() - a_timespan

​

Example

ago(6h)

Run in Playground

• Result:

{
  "date_time_functions": "2023-09-11T20:12:39Z"
}

ago(3d)

Run in Playground

• Result:

{
  "date_time_functions": "2023-09-09T02:13:29Z"
}

​

datetime_add()

Calculates a new datetime from a specified datepart multiplied by a specified amount, added to a specified datetime.

​

Arguments

• period: string.
• amount: integer.
• datetime: datetime value.

​

Returns

A date after a certain time/date interval has been added.

​

Example

datetime_add(period,amount,datetime)

['sample-http-logs']
| project new_datetime = datetime_add( "month", 1, datetime(2016-10-06))

Run in Playground

• Result:

{
  "new_datetime": "2016-11-06T00:00:00Z"
}

​

datetime_part()

Extracts the requested date part as an integer value.

​

Arguments

• date: datetime
• part: string

​

Returns

An integer representing the extracted part.

​

Examples

datetime_part(part,datetime)

['sample-http-logs']
| project new_datetime = datetime_part("Day", datetime(2016-06-26T08:20:03.123456Z))

Run in Playground

• Result:

{
  "new_datetime": 26
}

​

datetime_diff()

Calculates calendarian difference between two datetime values.

​

Arguments

• period: string.
• datetime_1: datetime value.
• datetime_2: datetime value.

​

Returns

An integer, which represents amount of periods in the result of subtraction (datetime_1 - datetime_2).

​

Example

datetime_diff(period,datetime_1,datetime_2)

['sample-http-logs']
| project new_datetime = datetime_diff("week", datetime(2019-06-26T08:20:03.123456Z), datetime(2014-06-26T08:19:03.123456Z))

Run in Playground

• Result:

{
  "new_datetime": 260
}

['sample-http-logs']
| project new_datetime = datetime_diff("week", datetime(2015-11-08), datetime(2014-11-08))

Run in Playground

• Result:

{
  "new_datetime": 52
}

​

dayofmonth()

Returns the integer number representing the day number of the given month

​

Arguments

• a_date: A datetime

​

Returns

day number of the given month.

​

Example

dayofmonth(a_date)

['sample-http-logs']
| project day_of_the_month = dayofmonth(datetime(2017-11-30))

Run in Playground

• Result:

{
  "day_of_the_month": 30
}

​

dayofweek()

Returns the integer number of days since the preceding Sunday, as a timespan.

​

Arguments

• a_date: A datetime.

​

Returns

The timespan since midnight at the beginning of the preceding Sunday, rounded down to an integer number of days.

​

Example

dayofweek(a_date)

['sample-http-logs']
| project day_of_the_week = dayofweek(datetime(2019-05-18))

Run in Playground

• Result:

{
  "day_of_the_week": 6
}

​

dayofyear()

Returns the integer number represents the day number of the given year.

​

Arguments

• a_date: A datetime.

​

Returns

day number of the given year.

​

Example

dayofyear(a_date)

['sample-http-logs']
| project day_of_the_year = dayofyear(datetime(2020-07-20))

Run in Playground

• Result:

{
  "day_of_the_year": 202
}

​

endofyear()

Returns the end of the year containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the end of the year for the given date value

​

Example

endofyear(date)

['sample-http-logs']
| extend end_of_the_year = endofyear(datetime(2016-06-26T08:20))

Run in Playground

• Result:

{
  "end_of_the_year": "2016-12-31T23:59:59.999999999Z"
}

​

getmonth()

Get the month number (1-12) from a datetime.

['sample-http-logs']
| extend get_specific_month = getmonth(datetime(2020-07-26T08:20))

​

getyear()

Returns the year part of the datetime argument.

​

Example

getyear(datetime())

['sample-http-logs']
| project get_specific_year = getyear(datetime(2020-07-26))

Run in Playground

• Result:

{
  "get_specific_year": 2020
}

​

hourofday()

Returns the integer number representing the hour number of the given date

​

Arguments

• a_date: A datetime.

​

Returns

hour number of the day (0-23).

​

Example

hourofday(a_date)

['sample-http-logs']
| project get_specific_hour = hourofday(datetime(2016-06-26T08:20:03.123456Z))

Run in Playground

• Result:

{
  "get_specific_hour": 8
}

​

endofday()

Returns the end of the day containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the end of the day for the given date value.

​

Example

endofday(date)

['sample-http-logs']
| project end_of_day_series = endofday(datetime(2016-06-26T08:20:03.123456Z))

Run in Playground

• Result:

{
  "end_of_day_series": "2016-06-26T23:59:59.999999999Z"
}

​

now()

Returns the current UTC clock time, optionally offset by a given timespan. This function can be used multiple times in a statement and the clock time being referenced will be the same for all instances.

​

Arguments

• offset: A timespan, added to the current UTC clock time. Default: 0.

​

Returns

The current UTC clock time as a datetime.

​

Example

now([offset])

['sample-http-logs']
| project returns_clock_time = now(-5d)

Run in Playground

• Result:

{
  "returns_clock_time": "2023-09-07T02:54:50Z"
}

​

endofmonth()

Returns the end of the month containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the end of the month for the given date value.

​

Example

endofmonth(date)

['sample-http-logs']
| project end_of_the_month = endofmonth(datetime(2016-06-26T08:20))

Run in Playground

• Result:

{
  "end_of_the_month": "2016-06-30T23:59:59.999999999Z"
}

​

endofweek()

Returns the end of the week containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the end of the week for the given date value

​

Example

endofweek(date)

['sample-http-logs']
| project end_of_the_week = endofweek(datetime(2019-04-18T08:20))

Run in Playground

• Result:

{
  "end_of_the_week": "2019-04-20T23:59:59.999999999Z"
}

​

monthofyear()

Returns the integer number represents the month number of the given year.

​

Arguments

• date: A datetime.

​

Returns

month number of the given year.

​

Example

monthofyear(datetime("2018-11-21"))

['sample-http-logs']
| project month_of_the_year = monthofyear(datetime(2018-11-11))

Run in Playground

• Result:

{
  "month_of_the_year": 11
}

​

startofday()

Returns the start of the day containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the start of the day for the given date value

​

Examples

startofday(datetime(2020-08-31))

['sample-http-logs']
| project start_of_the_day = startofday(datetime(2018-11-11))

Run in Playground

• Result:

{
  "start_of_the_day": "2018-11-11T00:00:00Z"
}

​

startofmonth()

Returns the start of the month containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the start of the month for the given date value

​

Example

['github-issues-event']
| project start_of_the_month =  startofmonth(datetime(2020-08-01))

Run in Playground

• Result:

{
  "start_of_the_month": "2020-08-01T00:00:00Z"
}

['hackernews']
| extend start_of_the_month = startofmonth(datetime(2020-08-01))

Run in Playground

• Result:

{
  "start_of_the_month": "2020-08-01T00:00:00Z"
}

​

startofweek()

Returns the start of the week containing the date

Start of the week is considered to be a Sunday.

​

Arguments

• date: The input date.

​

Returns

A datetime representing the start of the week for the given date value

​

Examples

['github-issues-event']
| extend start_of_the_week =  startofweek(datetime(2020-08-01))

Run in Playground

• Result:

{
  "start_of_the_week": "2020-07-26T00:00:00Z"
}

['hackernews']
| extend start_of_the_week = startofweek(datetime(2020-08-01))

Run in Playground

• Result:

{
  "start_of_the_week": "2020-07-26T00:00:00Z"
}

['sample-http-logs']
| extend start_of_the_week = startofweek(datetime(2018-06-11T00:00:00Z))

​

startofyear()

Returns the start of the year containing the date

​

Arguments

• date: The input date.

​

Returns

A datetime representing the start of the year for the given date value

​

Examples

['sample-http-logs']
| project yearStart = startofyear(datetime(2019-04-03))

Run in Playground

• Result:

{
  "yearStart": "2019-01-01T00:00:00Z"
}

['sample-http-logs']
| project yearStart = startofyear(datetime(2019-10-09 01:00:00.0000000))

Run in Playground

• Result:

{
  "yearStart": "2019-01-01T00:00:00Z"
}