CODEWITHSUNDEEP
azure-application-insights
Rick Strahl
Rick Strahl

Reputation: 17671

Azure Application Insights: How to format dates (and numbers)

I'm in need of formatting an aggregated date in such a way that it produces an readable and sortable format. I am creating a time series that aggregates the number of requests over a period of days using the following code:

let us_date = (t:datetime)
{ 
  strcat(    getmonth(t - 8h), "-",  dayofmonth(t - 8h))
};
requests
| project timestamp, client_IP, local_date = us_date(timestamp) 
| where timestamp >= now() -  30d  and timestamp <= now() + 1d
| summarize  uniqueUsers = dcount(client_IP) by usdate = us_date(timestamp)
| order by usdate desc

which produces:

enter image description here

The data is fine, but due to the formatting the sort order is off.

For this to sort correctly should look like 05-09, but I can't see a way to do this sort of formatting.

Note that I can do the following and get the data in the right order but ugly format:

let us_date = (t:datetime)
{ 
  strcat(   getyear(t - 8h) , '-', getmonth(t - 8h), "-",  dayofmonth(t - 8h))
};
requests
| project timestamp, client_IP 
| where timestamp >= now() -  30d  and timestamp <= now() + 1d
| summarize  uniqueUsers = dcount(client_IP) by usdate = bin(timestamp - 8h,1d)
| order by usdate desc

but this produces very verbose list view and also embeds the time into the chart.

enter image description here

Any ideas how to address either the number formatting issue or how to get another value into the query to allow sorting without throwing off the results?

Upvotes: 2

Views: 7672

Answers (2)

EranG
EranG

Reputation: 862

A bit ugly, but string parsing can do the trick here:

requests
| project timestamp, client_IP 
| where timestamp >= now() -  30d  and timestamp <= now() + 1d
| summarize  uniqueUsers = dcount(client_IP) by usdate = bin(timestamp - 8h,1d)
| parse tostring(usdate) with year "-" shortDate "T" *
| project shortDate, uniqueUsers** 
| order by shortDate desc

The solution lies in the parse line - taking only the month and day parts of the date

Upvotes: 0

arboreal84
arboreal84

Reputation: 2144

a) You can project after summarize to change the column format, column name or column order.

b) You can format the result of bin.

Upvotes: 1

Related Questions