CODEWITHSUNDEEP
azureazure-stream-analytics
Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

Truncated to the minute

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 :

enter image description here

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.

Upvotes: 0

Views: 61

Answers (2)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

I fixed this problem with following query

        SELECT
                DATETIMEFROMPARTS(
                    DATEPART(yyyy,DateAdd(minute,-1,System.TimeStamp)), 
                    DATEPART(mm,DateAdd(minute,-1,System.TimeStamp)),
                    DATEPART(dd,DateAdd(minute,-1,System.TimeStamp)), 
                    DATEPART(hh,DateAdd(minute,-1,System.TimeStamp)), 
                    DATEPART(mi,DateAdd(minute,-1,System.TimeStamp)), 0, 0)
                  As WinStart,
                DATETIMEFROMPARTS(
                    DATEPART(yyyy,System.TimeStamp), 
                    DATEPART(mm,System.TimeStamp),
                    DATEPART(dd,System.TimeStamp), 
                    DATEPART(hh,System.TimeStamp), 
                    DATEPART(mi,System.TimeStamp), 0, 0) As WinEnd,
                turnstile,
                COUNT(*) AS entries
        INTO [output-blobstorage]
        FROM [input-iot] TIMESTAMP BY entrytime
        GROUP BY 
            turnstile, 
            HoppingWindow (duration(second, 60),hop(second, 60))

enter image description here

Upvotes: 0

Jason H
Jason H

Reputation: 131

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

Upvotes: 1

Related Questions