Create date and time in azure SQL query for your sensors in IOT hub

Question

0.00/5 (No votes)

See more:

Hi Team

I am trying to find a way to figure it out as to how to create date although its not on sensors input for my IOT hub. But i want to cater this field as field for my sensors like humidity and temperature. That each sensor needs to have date per dataset for my power bi desktop.

What I have tried:

WITH iot_hub_last_3_mins AS (
    SELECT 
        System.TimeStamp() AS windowTime,
        deviceId,
        max(value) as maxTemp
    FROM
        PowerBIVisualizationOutput TIMESTAMP BY pwb1
    WHERE 
        sensorName = 'IOT Hub' 
    GROUP BY 
        deviceId, 
        SlidingWindow(minute, 3) 
)

SELECT 
    pwb1.t AS eventEnqueuedUctTime,
    pwb1.temperature, 
    pwb1.value AS temp,
	pwb1.maxtTemp AS maxTemp
    
INTO PowerBITable

FROM PowerBIVisualizationInput pwb1 TIMESTAMP BY PowerBIVisualizationOutput
JOIN iot_hub_last_3_mins PowerBIVisualizationOutput
    ON pwb1.temperature = PowerBIVisualizationOutput.deviceId 
    AND pwb1.t = PowerBIVisualizationOutput.windowTime
    AND DATEDIFF(minute,pwb1,PowerBIVisualizationOutput) between 0 and 10
    
WHERE
    pwb1.sensorName = 'temp'
    AND pwb1.value <= 40
    AND PowerBIVisualizationOutput.maxTemp > 10
SELECT 
    pwb1.t AS eventEnqueuedUctTime,
    pwb1.illuminance, 
    pwb1.value AS illum,
	pwb1.maxPower AS maxTemp
    
INTO PowerBITable

FROM PowerBIVisualizationInput pwb1 TIMESTAMP BY PowerBIVisualizationOutput
JOIN iot_hub_last_3_mins PowerBIVisualizationOutput
    ON pwb1.deviceId = PowerBIVisualizationOutput.deviceId 
    AND pwb1.t = PowerBIVisualizationOutput.windowTime
    AND DATEDIFF(minute,t1,t2) between 0 and 3
    
WHERE
    pwb1.sensorName = 'illum'
    AND pwb1.value <= 40
    AND PowerBIVisualizationOutput.maxTemp > 10

Posted 14-Oct-19 0:52am

gcogco10

Updated 15-Oct-19 0:36am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-10-14T01:17:00

Solution 1

One way to do it is to add a Default Values for Columns[^] which can include the date/time the row was inserted. Do that with a UTC default value, and there is no more work needed.

Posted 14-Oct-19 1:17am

OriginalGriff

gcogco10 · Answer 2 · 2019-10-15T00:37:00

Hi Team

I managed to have a working around this, but i don't get a data from the date field. My code is as follows; Is there a way can be able to get latest date and timestamp? Please help me i am almost there with this, thanks?

SELECT  
    illuminance as illuminance,
    temperature as temperature,
    MAX(CAST([timeStamp] AS datetime)) AS Date
INTO 
   [PowerBIVisualizationOutput] 
FROM 
   [PowerBIVisualizationInput]
   
GROUP BY 
illuminance,
temperature,
TumblingWindow(minute,5)