#7 動態查詢InfluxDB 資料庫 以及 用MQTT來遠端開/關繼電器

 大家好, 今天來和大家分享 “一起來學樹莓派”, 

本週的主題為: “#7 動態查詢InfluxDB 資料庫 以及 用MQTT來遠端開/關繼電器”.


上期的資料庫查詢的方式是固定時間軸的, 每次要查詢資料時, 可能都要重新下 SQL 指令.

本期要介紹的是使用 Slider 滑桿的方式來調整時間的區間, 並用 Interval 滑桿來調整時間的平均值, 使查詢資料的方法更便利. 


另外, 本次也介紹如何使用 MQTT out 來從遠端來開/關繼電器. 

(如果你要接上 110V or 220V 電器, 請自行小心高壓電的危險! 本人無法負責您的風險)


一起來學樹莓派吧!


這影片主要內容有:

  • 如何用 Slider 建立動態查詢 InfluxDB 的界面

  • 用 MQTT out 來遠端開/關繼電器 (可以接上電器開/關)

  • 如何把 SQL 讀出的資料解析送到 Dashboard Chart

  • 使用 Layout 工具來佈置 dashboard 排列位置

  • 使用 CSS template 來改變 dashboard 外觀


如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!




影片時間軸：

00:00 開始 00:34 課程開始 02:29 在 NodeRED 中開始寫程式 03:48 建立Dashboard (後來不小心被我刪除.. :( 05:27 將時間/平均時間 Sliders 加到立Dashboard 08:20 將變數儲存到 context.flow 10:45 解釋 SQL 命令的內容 11:50 從Flow 變數中取得 hours 變數內容 12:58 點擊Deploy儲存Nodes 14:29 使用 Influx client 在 terminal 上查看SQL的正確性 15:20 使用 Link in / Link out nodes 讓畫面更清楚 18:25 查看資料庫讀取後資料結構 20:33 使用 .toFixed(2) 設定小數點位數為 2 位 25:45 使用 Layout 工具來佈置 dashboard 排列位置 28:05 複製自其他 flow 的 Nodes 31:39 加入 Switch / MQTT out Nodes 34:39 解釋 ESP8266 + 繼電器程式碼 37:25 根據收到的 Message 來打開/關閉 relay 38:21 實機展示：用 MQTT來遠端打開/關閉繼電器 38:53 危險！ 注意高壓電可能會致命或使人受到傷害！！ 39:14 使用 Template (CSS) 來自行定義Dashboard 的外觀


Create query 程式碼(A): 

msg.query = "select mean(temp) as temp, mean(humd) as humd from studioTemp where location='studio' and time > now() - ";
msg.query += flow.get("hours") + "h GROUP by time(" + flow.get("interval") + "m) FILL(0)";

return msg;

Format DB data 程式碼(B): 

var My_series = ["Temperature °C", "Humidity %"];
var My_labels = ["temp", "Humd"];
var data = msg.payload;

// var data = msg.payload;
// Not sure the same query produced differnet results...?

temp = data.map(point=> {return {x:point.time , y:point.temp.toFixed(2)}});
humd = data.map(point=> {return {x:point.time , y:point.humd.toFixed(2)}});
var My_data = [temp, humd];

msg.payload = [{ series: My_series, data: My_data, labels: My_labels}]

return msg;

NodeRED source code is available here: Download it here.

Stonez56 一起來學樹莓派系列影片: 

參考資料:

Comments

Post a Comment

Popular posts from this blog

Arduino - DFPlayer Mini MP3 Module

Image
Arduino - Test DFPlayer Mini MP3 Module I found DFPlayer mini MP3 module on China auction site Taobao when I was looking for a MP3 player module for an Arduino projects.  This module is quite small and it only require few pins connections to Arduino and a speaker to work.  Looks really nice for my little project. Actually, the module is quite capable and complicated, if you were interested, please visit this  page . This tutorial here is just to show you how to connect DFPlayer module with Arduino and speaker and the Arduino sketch provided here just to play few sample sounds I collected from Internet. First, I recycled a 2W, 4Omh small speaker from this little music box. Material Needed: DFPlayer Mini module  Speaker - DFPlayer module support to to 3W SD card - 2GB ~ 32GB formatted with FAT or FAT32 MP3 / WAV - audio files Arduino mini Pro Few jump wires As you can see the illustration below I made in Power Point, hardware connection is quite simple (
Read more

Android control color RGB LED using HC-05 Bluetooth with Arduino (Part I)

Image
Android control color RGB LED using HC-05 Bluetooth with Arduino (Part I) Part II of this tutorial is here! Updated Sketch for ESP32 boards   https://stonez56.blogspot.com/2021/07/android-control-color-rgb-led-with.html This time, I am going to show you how to use Android phone to connect to HC-05 Bluetooth module and Arduino. Hardware needed: Arduino mini pro * 1 HC-05 Bluetooth module * 1 RGB LED * 2 Jump wires * 10 HC-05 Bluetooth Module wiring as follow: TX connect to Arduino Pin 11(RX) RX connect to Arduino Pin 12(TX) Remember to connect TX on the HC-05 to RX on the Arduino and RX on the HC-05 to TX on the Arduino, so Arduino can receive data correctly. VCC connect to 5V power GND connect to Ground RGB LED wiring as follow: Warning: You must connect Arduino mini pro, so your PWM Pin can control LED’s color! For example, Arduino mini pro only has 6 PWM pins and they are 3, 5, 6, 9, 10, and 11, other pins do not have PWM. Refer to Wikipedia for PWM R co
Read more

Arduino #27 AsyncWifimanager ElegantOTA ESP32 (WiFi Password Mgnt. + WiFi Firmware Update)

Image
*** If you are using the ESP8266, Please see this:  ESP8266 It would be great if every IoT device deployed with OTA capability with Wifimanager.  Then the IoT device would be able to change SSID & password without connecting the device to the computer to update SSID and password as well as FOTA at any time when you wanted. In this tutorial, let’s combine ESPAsync_Wifimanager and AsyncElegantOTA into a code base to become a handy tool for future IoT projects!  I have made WiFiManager & AsyncElegantOTA tutorial videos. If you don't know what they are and how they work for IoT projects, I strongly recommend you to watch these videos first. This time, I used ESPAsync_WiFiManager library, it's because WiFiManager does not work with AsyncElegnatOTA together. We have to change to ESPAsync_WiFiManager.  The main reason for this is that AsnycWiFiManager is using Asynchronous technology. Which means it can accept connections from different sources at the same time. So, while WiFi
Read more