locked
How to create a date time in your sensors sql query? RRS feed

  • Question

  • Hi Team

    I have this code in my azure sql query;

    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)

    The code output sensors namely, temperature and illuminance data, But i want to do the same with date, how can i create date field with data by having current timestamp?

    Thursday, October 17, 2019 5:26 AM

All replies

  • Hello,

    You may try the below query that saves data as follows:

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

    Hope this helps.      

    ----------------------------------------------------------------------------------------

    Do click on "Mark as Answer" and Upvote on the post that helps you, this can be beneficial to other community members.

    Thursday, October 17, 2019 6:53 AM
  • I have tried that logic before, the problem is for me getting data back on that field. I dont know as where to improve specifically, please guide me on this process. 

    Thursday, October 17, 2019 8:04 AM
  • Hi TaGcobs2010,

    Can you please let us know how are you expecting the output to be ?
    If you can paste the input data and the expected output , that will help us understand the ask .

    Thanks Himanshu

    Monday, October 21, 2019 4:55 PM