I have a column,['timestamp'] in Azure Data Explorer. A certain value, x, is assigned to this timestamp.
Data is being streamed in real time and I would like to take the last value of yesterday's data.
However, when I do a substring() or datetime formatting, for some reason, it changes the date from 2022-08-24 to 2022-08-23.
Code:
datasource
| extend ts1 = (datetime_add('day',-1, todatetime(timestamp)))
| extend ts2 = substring(ts1,0,10)
| extend ts3 = format_datetime(todatetime(ts1), 'yyyy-MM-dd')
| project timestamp, ts1,ts2,ts3
| sort by timestamp desc
Output:
| timestamp | ts1 | ts2 | ts3 |
|---|---|---|---|
| 2022-08-25 10:56:09.000 | 2022-08-24 10:56:09.000 | 2022-08-24 | 2022-08-24 |
| 2022-08-25 08:56:09.000 | 2022-08-24 08:56:09.000 | 2022-08-24 | 2022-08-24 |
| 2022-08-25 07:26:09.000 | 2022-08-24 07:26:09.000 | 2022-08-23 | 2022-08-23 |
| 2022-08-25 05:56:09.000 | 2022-08-24 05:56:09.000 | 2022-08-23 | 2022-08-23 |
Required output:
ts2 and ts3 to display 2022-08-24 instead of 2022-08-23
Please review my code and output and help pls.
CodePudding user response：
We'll do it in two parts :-)
Part 1: Understanding the issue.
The ADX 
