Truncated to the minute

Question

I have written this code in my "Azure Stream Analytics" job.

SELECT
    DateAdd(minute,-1,System.TimeStamp) AS WinStart,
    System.TimeStamp AS WinEnd,
    turnstile,
    COUNT(*) AS entries
INTO [output-blobstorage]
FROM [input-iot] TIMESTAMP BY entrytime
GROUP BY turnstile,  SlidingWindow (duration(second, 60))

I would like to have "WinStart" and "WinEnd" truncated to minute.

For exempl: If WinStart is "2018-06-08T18:15:01.5160000Z" i would like to have "2018-06-08T18:15:00.0000000Z"

This is my result file :

In other hand I would like to group by my result by "turnstile" and SlidingWindows based in minute.

But now I have secound and mi-sec in both first columns.

Answer 1

Maybe combine two - Decomposing the date using DatePart and composing it back using DateTimeFromParts and using zero for the seconds and milliseconds.

DATETIMEFROMPARTS(DATEPART(yyyy,WinStart), 
     DATEPART(mm,WinStart),DATEPART(dd,WinStart), DATEPART(hh,WinStart), 
     DATEPART(mi,WinStart), 0, 0) As WinStartRounded

Or do you want the HoppingWindow instead of SlidingWindow? so that the windows aligned to a 1 minute boundary:

Thanks, Jason