Smart imba: Isu tinovaka magirafu emvura uye mashandisirwo emagetsi muMubatsiri Wepamba

Pese pandinowana muripo wemagetsi nemvura, ndinoshamisika – mhuri yangu inodya zvakanyanya kudaro here? Zvakanaka, hongu, imba yekugezera ine pasi inopisa uye bhairi, asi haipisi moto nguva dzose. Isu tinoita sekunge tiri kuchengetedza mvura (kunyangwe isu tichidawo kupfapfaidza mukamuri yekugezera). Makore akati kuti apfuura ini ndatova yakabatana mamita emvura и magetsi kune imba yakangwara, asi ndipo pakanamatira zvinhu. Isu tangosvika pakuongorora mashandisirwo, izvo zviri izvo chinyorwa ichi chaizvo.

Nguva pfupi yadarika ndakachinjira kuMubatsiri Wepamba seyangu smart imba system. Chimwe chezvikonzero waive mukana chaiwo wekuronga kuunganidzwa kwehuwandu hwedata nekugona kugadzira zviri nyore marudzi akasiyana emagrafu.

Ruzivo rwakatsanangurwa muchinyorwa ichi hachizi chitsva; zvese izvi pasi pemasosi akasiyana zvakatotsanangurwa paInternet. Asi chinyorwa chimwe nechimwe chinowanzotsanangura nzira imwe chete kana kuti chimiro. Ndaifanira kuenzanisa nzira idzi dzose uye ndinosarudza iyo yakanyatsokodzera ini. Chinyorwa ichi hachisati chapa ruzivo rwakakwana pakuunganidzwa kwedata, asi imhando yepfupiso yekuti ndakazviita sei. Saka kutsoropodza kunovaka uye mazano ekuvandudza anogamuchirwa.

Kugadzirwa kwedambudziko

Saka, chinangwa chechiitiko chemazuva ano ndechekuwana magirafu akanaka emvura uye kushandiswa kwemagetsi:

• Hourly kwemazuva maviri
• Zuva nezuva kwemavhiki maviri
• (kusarudza) vhiki nevhiki uye pamwedzi

Pane zvimwe zvinonetsa neizvi:

• Zvikamu zvechati zvakajairika kazhinji hazvina kunaka. Pazvakanakisa, iwe unogona kuvaka mutsara girafu poindi nepoindi.

  Kana iwe ukatarisa zvakaoma zvakakwana, iwe unogona kuwana yechitatu-bato zvikamu zvinowedzera kugona kweiyo standard chart. Kune mubatsiri weimba, mumutemo, ichi chinhu chakanaka uye chakanaka chikamu mini-graph kadhi, asi zvakare ine zvishoma zvishoma:

  • Zvakaoma kuseta maparamendi echati yebhawa pamusoro penguva dzakakura (hupamhi hwebhawa hwakaiswa muzvikamu zveawa, zvinoreva kuti nguva yakareba kupfuura awa ichaiswa muzvikamu zvishoma)
  • Iwe haugone kuwedzera masangano akasiyana kune imwe girafu (semuenzaniso, tembiricha uye humidity, kana kusanganisa bhara girafu nemutsara)
• Kwete chete mubatsiri wepamba nekusarudzika anoshandisa yakanyanya primitive SQLite dhatabhesi (uye ini, murume anoshanda, ndaisakwanisa kubata kuisa MySQL kana Postgres), asi iyo data haina kuchengetwa nenzira yakanyanya kunaka. Saka, semuenzaniso, pese paunochinja nyangwe diki diki diki diki dhijitari yeparamita, hombe json inosvika kilobyte muhukuru inonyorerwa kudhatabhesi.

  {"entity_id": "sensor.water_cold_hourly", "old_state": {"entity_id": "sensor.water_cold_hourly", "state": "3", "attributes": {"source": "sensor.water_meter_cold", "status": "collecting", "last_period": "29", "last_reset": "2020-02-23T21:00:00.022246+02:00", "meter_period": "hourly", "unit_of_measurement": "l", "friendly_name": "water_cold_hourly", "icon": "mdi:counter"}, "last_changed": "2020-02-23T19:05:06.897604+00:00", "last_updated": "2020-02-23T19:05:06.897604+00:00", "context": {"id": "aafc8ca305ba4e49ad4c97f0eddd8893", "parent_id": null, "user_id": null}}, "new_state": {"entity_id": "sensor.water_cold_hourly", "state": "4", "attributes": {"source": "sensor.water_meter_cold", "status": "collecting", "last_period": "29", "last_reset": "2020-02-23T21:00:00.022246+02:00", "meter_period": "hourly", "unit_of_measurement": "l", "friendly_name": "water_cold_hourly", "icon": "mdi:counter"}, "last_changed": "2020-02-23T19:11:11.251545+00:00", "last_updated": "2020-02-23T19:11:11.251545+00:00", "context": {"id": "0de64b8af6f14bb9a419dcf3b200ef56", "parent_id": null, "user_id": null}}}

  Ndine akawanda ma sensors (tembiricha sensors mukamuri yega yega, mvura uye magetsi metres), uye mamwe anogadzirawo data rakawanda. Semuyenzaniso, iyo SDM220 yemagetsi mita chete inogadzira anenge gumi nemaviri maitiro masekonzi ese gumi-10, uye ndinoda kuisa anenge 15 metres akadaro. Kune zvakare boka rese rema paramita anoverengerwa zvichibva pane mamwe ma sensor. Izvozvo. ese aya maitiro anogona kupinza zviri nyore dhatabhesi ne8-100 MB zuva nezuva. Muvhiki iyo sisitimu yacho haichafambi, uye mumwedzi iyo flash drive ichafa (munyaya yeyakajairwa mubatsiri wemba yekuisa paRaspberry PI), uye kuchengetedza data kwegore rose hakuna mubvunzo.

• Kana iwe uine rombo rakanaka, mita yako inogona kuverenga kushandiswa kwayo pachayo. Iwe unogona kutendeukira kumita chero nguva uye kubvunza kuti inguva yakaunganidzwa kukosha kwekushandisa. Sezvo mutemo, ese magetsi mamita ane digital interface (RS232 / RS485 / Modbus / Zigbee) anopa mukana uyu.

  Zvakatonyanya kuipa kana mudziyo uchikwanisa kuyera imwe ipapo ipapo paramende (semuenzaniso, simba repakarepo kana zvazvino), kana kungogadzira pulses yega yega X watt-maawa kana malita. Ipapo iwe unofanirwa kufunga nezvekuti sei uye nei kuti uisanganise uye kupi kuti uunganidze kukosha. Pane njodzi yekupotsa mushumo unotevera chero chikonzero, uye kurongeka kwehurongwa sese kunomutsa mibvunzo. Iwe unogona, hongu, kuisa zvese izvi kune yakangwara imba sisitimu semubatsiri wemba, asi hapana akadzima poindi yehuwandu hwemarekodhi mudhatabhesi, uye hazvizoite kuvhota masensa kanopfuura kamwe sekondi (a. kudzikisira kwemubatsiri weimba yekuvaka).

Nzira 1

Kutanga, ngationei izvo mubatsiri wepamba anopa kunze kwebhokisi. Kuyera kushandiswa kwekanguva chinhu chinotsvakwa zvakanyanya. Ehe, yakaitwa kare kare muchimiro chehunyanzvi chikamu - utility_meter.

Chinokosha chechikamu ndechekuti mukati inogadzira shanduko ikozvino_accumulated_value uye inogadzirisa zvakare mushure menguva yakatarwa (awa / vhiki / mwedzi). Iyo chikamu pachayo inotarisisa iyo yekuisa shanduko (kukosha kweimwe sensor), inonyoresa pachayo kune shanduko mukukosha - iwe unongowana mhedzisiro yapera. Chinhu ichi chinotsanangurwa mumitsetse mishoma mufaira rekugadzirisa

utility_meter:
  water_cold_hour_um:
    source: sensor.water_meter_cold
    cycle: hourly
  water_cold_day_um:
    source: sensor.water_meter_cold
    cycle: daily

Pano sensor.water_meter_cold ndiyo iripo ikozvino kukosha kwemamita mumalita andinogamuchira zvakananga kubva pachidimbu chesimbi by mqtt. Iyo dhizaini inogadzira maviri ma sensors matsva emvura_cold_hour_um uye mvura_cold_day_um, ayo anounganidza kuverenga kweawa uye zuva nezuva, achiadzosera ku zero mushure mekunge nguva yapera. Heino girafu yebhatiri yeawa kwehafu yezuva.

Iyo kodhi yeawa uye zuva nezuva machati e lovelace-UI inotaridzika seizvi:

      - type: history-graph
        title: 'Hourly water consumption using vars'
        hours_to_show: 48
        entities:
          - sensor.water_hour

      - type: history-graph
        title: 'Daily water consumption using vars'
        hours_to_show: 360
        entities:
          - sensor.water_day

Chaizvoizvo, dambudziko neiyi nzira iri mune iyi algorithm. Sezvandambotaura, kune yega yega kukosha kwekuisa (ikozvino mita kuverenga kune imwe neimwe inotevera litre) 1kb yemarekodhi inogadzirwa mudhatabhesi. Imwe neimwe yekushandisa mita zvakare inoburitsa kukosha kutsva, iyo inowedzerwa kune hwaro. Kana ini ndichida kuunganidza kuverenga kweawa / zuva nezuva / vhiki nevhiki / mwedzi, uye nokuda kwekukwira kwemvura yakawanda, uye kuwedzera pakiti yemamita emagetsi, iyo ichava yakawanda data. Zvakanaka, zvakanyatsojeka, hapana data yakawanda, asi sezvo mubatsiri weimba anonyora ruzivo rusingakoshi kune dhatabhesi, saizi yedatabase ichakura nekusvetuka uye miganhu. Ndinotya kunyange kufungidzira saizi yehwaro hwevhiki nemwedzi machati.

Uye zvakare, iyo yekushandisa mita pachayo haigadzirise dambudziko. Iyo girafu yemakoshero anogadzirwa neiyo yekushandisa mita ndeye monotonically inowedzera basa iro rinodzosera ku0 awa yega yega. Tinoda chati yekushandisa inonzwisisika kumushandisi, inoratidza kuti mangani marita akadyiwa panguva iyi. Iyo yakajairwa nhoroondo-girafu chikamu haigone kuita izvi, asi mini-girafu-kadhi yekunze chikamu chinogona kutibatsira.

Iyi ndiyo kadhi kodhi yelovelace-UI:

      - aggregate_func: max
        entities:
          - color: var(--primary-color)
            entity: sensor.water_cold_hour_um
        group_by: hour
        hours_to_show: 48
        name: "Hourly water consumption aggregated by utility meter"
        points_per_hour: 1
        show:
          graph: bar
        type: 'custom:mini-graph-card'

Pamusoro pezvimiro zvakajairwa sezita re sensor, rudzi rwegirafu, ruvara (ini ndaisada iyo orenji yakajairwa), zvakakosha kuti ucherechedze marongero matatu:

• boka_by:awa - girafu ichagadzirwa nemabhawa anoenderana nekutanga kweawa
• mapoinzi_per_awa: 1 - bhaa imwe kweawa yega yega
• Uye zvakanyanya kukosha, aggregate_func: max - tora iyo yakanyanya kukosha mukati meawa yega yega. Iyi ndiyo parameter inoshandura girafu yesawtooth kuita mabara

Usateerere kune mutsara wemakoramu kuruboshwe - iyi ndiyo yakajairika maitiro echikamu kana pasina data. Asi pakanga pasina data - ndakangobatidza utility mita yekuunganidza maawa mashoma apfuura nekuda kwechinyorwa ichi (ini ndichatsanangura maitiro angu azvino zvishoma kudzika).

Mumufananidzo uyu ndaida kuratidza kuti dzimwe nguva iyo data inoratidza inotoshanda uye mabhawa anoratidza chaiwo maitiro. Asi handizvo zvoga. Nokuda kwechimwe chikonzero, iyo koramu yakasarudzwa yenguva kubva 11 kusvika 12 am inoratidza 19 litres, kunyange pazino girafu yakakwirira zvishoma kwenguva imwechete kubva kune imwechete sensor tinoona kushandiswa kwe62 litres. Pamwe pane tsikidzi kana kuti maoko akakombama. Asi ini handisati ndanzwisisa kuti sei data riri kurudyi rakaputsika - kushandiswa kwaive kwakajairwa, izvo zvinoonekwawo kubva kune toothy graph.

Kazhinji, handina kukwanisa kuwana kunzwisisika kweiyi nzira - girafu rinenge rinogara richiratidza rumwe rudzi rwekunyepedzera.

Yakafanana kodhi yemasikati sensor.

      - aggregate_func: max
        entities:
          - color: var(--primary-color)
            entity: sensor.water_cold_day_um
        group_by: interval
        hours_to_show: 360
        name: "Daily water consumption aggregated by utility meter"
        points_per_hour: 0.0416666666
        show:
          graph: bar
        type: 'custom:mini-graph-card'

Ndokumbira utarise kuti boka_by parameter rakagadzirirwa kupindirana, uye mapoinzi_per_hour parameter inotonga zvese. Uye imomo mune rimwe dambudziko nechikamu ichi - points_per_hour inoshanda zvakanaka pamachati eawa imwe chete kana pasi, asi inoyamwa panguva huru. Saka kuti ndiwane koramu imwe muzuva rimwe chete, ndaifanira kuisa kukosha 1/24=0.04166666. Handisi kutaura nezvemachati evhiki nemwedzi.

Nzira 2

Ndichiri kunzwisisa mubatsiri wepamba, ndakaona iyi vhidhiyo:

Shamwari inounganidza data rekushandisa kubva kune akati wandei marudzi eXiaomi sockets. Basa rake rakareruka zvishoma - kungoratidza kukosha kwekushandisa kwanhasi, nezuro uye pamwedzi. Hapana zvirongwa zvinodiwa.

Ngatisiyei padivi nhaurirano nezve kubatanidzwa kwemanyorero epakarepo simba kukosha - Ndakatonyora pamusoro pe "kurongeka" kweiyi nzira. Hazvina kujeka kuti sei asina kushandisa iyo yakaunganidzwa madhiri ekushandisa, ayo atounganidzwa nechekare. Mune maonero angu, kubatanidzwa mukati me hardware kuchashanda zviri nani.

Kubva pavhidhiyo tichatora pfungwa yekuverenga mashandisiro nemaoko kwenguva. Mukomana anongoverenga hunhu hwanhasi nezuro, asi isu tichaenda mberi uye kuyedza kudhirowa girafu. Chinokosha chenzira yakarongwa munyaya yangu ndeyotevera.

Ngatigadzire inoshanduka kukosha_at_the_beginning_of_awa, umo tichanyora kuverenga kwemamita kwazvino.
Tichishandisa timer, pakupera kweawa (kana pakutanga kweinotevera) tinoverenga mutsauko pakati pekuverenga ikozvino uye iyo yakachengetwa pakutanga kweawa. Musiyano uyu uchave kushandiswa kweawa iripo - isu tichachengetedza kukosha mukati me sensor, uye mune ramangwana tichavaka girafu zvichienderana nekukosha uku.
Iwe zvakare unofanirwa "kugadzirisa" kukosha_at_beginning_of_hour kusiyanisa nekunyora ikozvino counter kukosha ikoko.

Zvose izvi zvinogona kuitwa kuburikidza nemubatsiri weimba pachake.

Iwe uchafanirwa kunyora kodhi shoma pane yaimbove nzira. Kutanga, ngatitangei aya ma "variables" akafanana. Kunze kwebhokisi isu hatina "variable" sangano, asi isu tinogona kushandisa masevhisi eiyo mqtt broker. Isu tichatumira kukosha ikoko ne retain = rechokwadi mureza - izvi zvichachengetedza kukosha mukati memubhuroka, uye inogona kudhonzwa kubva ipapo chero nguva, kunyangwe kana mubatsiri wemba avhurwazve. Ndakagadzira macounter eawa nemazuva ese kamwechete.

- platform: mqtt
  state_topic: "test/water/hour"
  name: water_hour
  unit_of_measurement: l

- platform: mqtt
  state_topic: "test/water/hour_begin"
  name: water_hour_begin
  unit_of_measurement: l

- platform: mqtt
  state_topic: "test/water/day"
  name: water_day
  unit_of_measurement: l

- platform: mqtt
  state_topic: "test/water/day_begin"
  name: water_day_begin
  unit_of_measurement: l

Ese mashiripiti anoitika mune otomatiki, iyo inomhanya awa yega nehusiku hwese zvichiteerana.

- id: water_new_hour
  alias: water_new_hour
  initial_state: true
  trigger:
    - platform: time_pattern
      minutes: 0
  action:
    - service: mqtt.publish
      data:
        topic: "test/water/hour"
        payload_template: >
          {{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_hour_begin.state|int) }}
        retain: true
    - service: mqtt.publish
      data:
        topic: "test/water/hour_begin"
        payload_template: >
          {{ states.sensor.water_meter_cold.state }}
        retain: true

- id: water_new_day
  alias: water_new_day
  initial_state: true
  trigger:
    - platform: time
      at: "00:00:00"
  action:
    - service: mqtt.publish
      data:
        topic: "test/water/day"
        payload_template: >
          {{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_day_begin.state|int) }}
        retain: true
    - service: mqtt.publish
      data:
        topic: "test/water/day_begin"
        payload_template: >
          {{ states.sensor.water_meter_cold.state }}
        retain: true

Ose ari maviri otomatiki anoita 2 zviito:

• Verenga kukosha kwepakati semusiyano pakati pekutanga nekuguma
• Gadziridza kukosha kwekutanga kwenguva inotevera

Kuvakwa kwemagirafu mune iyi kesi inogadziriswa neyakajairika nhoroondo-girafu:

      - type: history-graph
        title: 'Hourly water consumption using vars'
        hours_to_show: 48
        entities:
          - sensor.water_hour

      - type: history-graph
        title: 'Daily water consumption using vars'
        hours_to_show: 360
        entities:
          - sensor.water_day

Zvinoita seizvi:

In musimboti, izvi zvatova izvo zvinodiwa. Chakanakira nzira iyi ndechekuti iyo data inogadzirwa kamwe chete panguva. Avo. 24 chete marekodhi pazuva kweawa yega chati.

Nehurombo, izvi hazvisati zvagadzirisa dambudziko rekukura kwehwaro. Kana ini ndichida girafu rekushandisa pamwedzi, ndichafanira kuchengetedza data kweinenge gore. Uye sezvo mubatsiri wepamba achipa imwe chete yekuchengetera nguva yekumisikidza kwese dhatabhesi, izvi zvinoreva kuti YESE data musystem ichafanirwa kuchengetwa kwegore rose. Semuenzaniso, mugore ndinodya 200 cubic metres emvura, zvinoreva kuti izvi zvinoreva 200000 zvinyorwa mudhatabhesi. Uye kana iwe ukafunga nezve mamwe ma sensors, ipapo nhamba yacho inova isina kufanira.

Nzira 3

Neraki, vanhu vakangwara vakatogadzirisa dambudziko iri nekunyora iyo InfluxDB dhatabhesi. Iyi dhatabhesi yakagadziridzwa zvakanyanya kuchengetedza nguva-yakavakirwa data uye yakanakira kuchengetedza kukosha kweakasiyana sensors. Iyo sisitimu inopawo SQL-senge mitauro yemubvunzo iyo inokutendera iwe kuti ubvise kukosha kubva mudhatabhesi wobva waaunganidza nenzira dzakasiyana. Pakupedzisira, data yakasiyana inogona kuchengetwa kwenguva dzakasiyana. Semuenzaniso, kuverenga kunogara kuchichinja senge tembiricha kana hunyoro hunogona kuchengetwa kwemavhiki mashoma, nepo kuverenga kwezuva nezuva kushandiswa kwemvura kunogona kuchengetwa kwegore rose.

Kunze kweInfluxDB, vanhu vakangwara vakagadzirawo Grafana, sisitimu yekudhirowa magirafu zvichienderana nedata kubva kuInfluxDB. Grafana inogona kudhirowa marudzi akasiyana emagirafu, kuagadzirisa zvakadzama, uye, zvinonyanya kukosha, magirafu aya anogona "kuvharirwa" pane lovelace-UI mubatsiri wepamba.

Wana kurudziro pano и pano. Zvinyorwa zvinotsanangura zvakadzama maitiro ekuisa nekubatanidza InfluxDB neGrafana kumubatsiri wepamba. Ini ndichatarisa pakugadzirisa dambudziko rangu chairo.

Saka, chekutanga pane zvese, ngatitange kuwedzera iyo counter kukosha muinfluxDB. Chidimbu chegadziriso yemubatsiri wemba (mumuenzaniso uyu ndichanakidzwa kwete nekutonhora chete, asiwo mvura inopisa):

influxdb:
  host: localhost
  max_retries: 3
  default_measurement: state
  database: homeassistant
  include:
    entities:
      - sensor.water_meter_hot
      - sensor.water_meter_cold

Ngatiregei kuchengetedza iyi data kune yemukati mubatsiri dhatabhesi kuitira kuti tisaiputire zvakare:

recorder:
  purge_keep_days: 10
  purge_interval: 1
  exclude:
    entities:
      - sensor.water_meter_hot
      - sensor.water_meter_cold

Ngatiendei kuInfluxDB console uye tigadzirise database yedu. Kunyanya, iwe unofanirwa kugadzirisa kuti imwe data ichachengetwa kwenguva yakareba sei. Izvi zvinodzorwa nezvinonzi. retention policy - izvi zvakafanana nedatabase mukati medura re database, imwe neimwe yemukati dhatabhesi ine zvigadziriso zvayo. Nekusagadzikana, data rese rinochengetwa mugwaro rekuchengetedza rinonzi autogen; iyi data ichachengetwa kwevhiki. Ndinoda kuti data reawa richengetwe kwemwedzi, data revhiki richengetwe kwegore, uye data repamwedzi risambodzimwa. Ngatigadzire mutemo wekuchengetedza wakakodzera

CREATE RETENTION POLICY "month" ON "homeassistant" DURATION 30d REPLICATION 1
CREATE RETENTION POLICY "year" ON "homeassistant" DURATION 52w REPLICATION 1
CREATE RETENTION POLICY "infinite" ON "homeassistant" DURATION INF REPLICATION 1

Ikozvino, kutaura zvazviri, hunyengeri hukuru ndeyekuunganidza data uchishandisa inoenderera kubvunza. Iyi inzira inomhanyisa mubvunzo panguva dzakatarwa, inounganidza data yemubvunzo uyu, uye inowedzera mubairo muhukoshi hutsva. Ngatitarisei muenzaniso (ndinonyora mukoramu kuti iverengeke, asi muchokwadi ndaifanira kuisa uyu murairo mumutsara mumwe)

CREATE CONTINUOUS QUERY cq_water_hourly ON homeassistant 
BEGIN 
  SELECT max(value) AS value 
  INTO homeassistant.month.water_meter_hour 
  FROM homeassistant.autogen.l 
  GROUP BY time(1h), entity_id fill(previous) 
END

Uyu murairo:

• Inogadzira mubvunzo unoenderera unonzi cq_water_cold_hourly mudura rekuchengetedza imba
• Chikumbiro chichaitwa awa yega yega (nguva (1h))
• Chikumbiro chichakwenya data rese kubva pachiyero 'homeassistant.autogen.l (marita), kusanganisira kuverenga kwemvura inotonhora uye inopisa.
• Iyo data yakaunganidzwa ichaiswa muboka ne entity_id, izvo zvinotipa zvakaparadzana kukosha kwemvura inotonhora uye inopisa.
• Sezvo iyo litre counter iri monotonically inowedzera kutevedzana mukati meawa yega yega, zvichave zvakakodzera kutora iyo yakanyanya kukosha, saka kuunganidza kuchaitwa nebasa max(value)
• Hutsva hutsva huchanyorerwa kuhomeassistant.month.water_meter_hour, apo mwedzi ndiro zita reretention policy ine nguva yekuchengeta mwedzi mumwe chete. Uyezve, data pamusoro pemvura inotonhora uye inopisa ichaparadzirwa mumarekodhi akapatsanurwa ane anowirirana entity_id uye kukosha mundima yekukosha.

Usiku kana pasina munhu pamba, hapana kushandiswa kwemvura, uye naizvozvo hapana zvinyorwa zvitsva mu homeassistant.autogen.l. Kuti udzivise kushaya kukosha mumibvunzo yenguva dzose, unogona kushandisa fill (yapfuura). Izvi zvinomanikidza InfluxDB kushandisa kukosha kweawa yekupedzisira.

Nehurombo, kuenderera kubvunza kune peculiarity: iyo yekuzadza (yapfuura) trick haishande uye marekodhi haana kungogadzirwa. Uyezve, iyi ndeimwe mhando yedambudziko risingakundike iro yave ichikurukurwa kwemakore anoverengeka zvino. Tichagadzirisa dambudziko iri gare gare, asi rega kuzadza (yapfuura) kuve mumubvunzo unoenderera - haipindire.

Ngatitarisei zvakaitika (zvechokwadi, unofanirwa kumirira maawa mashoma):

> select * from homeassistant.month.water_meter_hour group by entity_id
...
name: water_meter_hour
tags: entity_id=water_meter_cold
time                 value
----                 -----
...
2020-03-08T01:00:00Z 370511
2020-03-08T02:00:00Z 370513
2020-03-08T05:00:00Z 370527
2020-03-08T06:00:00Z 370605
2020-03-08T07:00:00Z 370635
2020-03-08T08:00:00Z 370699
2020-03-08T09:00:00Z 370761
2020-03-08T10:00:00Z 370767
2020-03-08T11:00:00Z 370810
2020-03-08T12:00:00Z 370818
2020-03-08T13:00:00Z 370827
2020-03-08T14:00:00Z 370849
2020-03-08T15:00:00Z 370921

Ziva kuti hunhu huri mudhatabhesi hwakachengetwa muUTC, saka runyoro urwu rwunosiyana nemaawa matatu - iwo 3am kukosha muInfluxDB inobuda inoenderana negumi am maitiro mumagirafu ari pamusoro. Ziva zvakare kuti pakati pa 7 na 10 am hapana marekodhi - ichi ndicho chinhu chimwechete chekuramba uchibvunza.

Sezvauri kuona, iyo yakasanganiswa kukosha ndeye monotonically inowedzera kutevedzana, mapindiro chete anoitika zvishoma kazhinji - kamwe paawa. Asi iri harisi dambudziko - tinogona kunyora mumwe mubvunzo unozotora iyo data chaiyo yegirafu.

SELECT difference(max(value)) 
FROM homeassistant.month.water_meter_hour 
WHERE entity_id='water_meter_cold' and time >= now() -24h 
GROUP BY time(1h), entity_id 
fill(previous)

Ini ndichatsanangura:

• Kubva kuhomeassistant.month.water_meter_hour dhatabhesi tichatora data ye entity_id='water_meter_cold' yezuva rekupedzisira (nguva >= now() -24h).
• Sezvandambotaura, zvimwe zvinyorwa zvinogona kushaikwa muchirongwa chehomeassistant.month.water_meter_hour. Tichagadzirazve data iri nekumhanyisa mubvunzo neGROUP BY nguva(1h). Ino nguva yekuzadza (yapfuura) ichashanda sezvaitarisirwa, ichigadzira iyo isipo data (basa rinotora kukosha kwapfuura)
• Chinhu chinonyanya kukosha muchikumbiro ichi ibasa rekusiyana, iro richaverenga mutsauko pakati peawa mamaki. Iyo haishande yega uye inoda kubatanidza basa. Rega iyi ive iyo max() yakashandiswa kare.

Mhedzisiro yekuuraya inoita seizvi

name: water_meter_hour
tags: entity_id=water_meter_cold
time                 difference
----                 ----------
...
2020-03-08T02:00:00Z 2
2020-03-08T03:00:00Z 0
2020-03-08T04:00:00Z 0
2020-03-08T05:00:00Z 14
2020-03-08T06:00:00Z 78
2020-03-08T07:00:00Z 30
2020-03-08T08:00:00Z 64
2020-03-08T09:00:00Z 62
2020-03-08T10:00:00Z 6
2020-03-08T11:00:00Z 43
2020-03-08T12:00:00Z 8
2020-03-08T13:00:00Z 9
2020-03-08T14:00:00Z 22
2020-03-08T15:00:00Z 72

Kubva 2 kusvika 5 am (UTC) pakanga pasina chekushandisa. Zvakadaro, mubvunzo uchadzosa kukosha kwakafanana kwekushandisa kuvonga kuzadza (yapfuura), uye musiyano webasa uchabvisa kukosha uku kubva pachezvayo uye zvinobuda zvichave 0, zvinova ndizvo chaizvo zvinodiwa.

Chasara kugadzira girafu. Kuti uite izvi, vhura Grafana, vhura imwe iripo (kana gadzira nyowani) dhibhodhi, uye gadzira pani nyowani. Zvirongwa zvechati zvichave seizvi.

Ini ndicharatidza data inotonhora uye inopisa mvura pane imwechete girafu. Chikumbiro chacho chakangofanana nezvandatsanangura pamusoro apa.

Maparamita ekuratidzira akaiswa sezvinotevera. Kwandiri ichava girafu ine mitsetse, iyo inofamba mumatanho (matanho). Ini ndichatsanangura iyo Stack parameter pazasi. Pane dzimwe nzira mbiri dzekuratidzira pazasi, asi hadzinakidze kudaro.

Kuti uwedzere mhedzisiro chati kumubatsiri wepamba unofanirwa:

• buda mukugadzirisa chati. Nechimwe chikonzero, iwo chaiwo machati ekugovera marongero anopihwa chete kubva kune dashboard peji
• Dzvanya pakatatu padivi pezita rechati uye sarudza share kubva mumenyu
• Muhwindo rinovhurwa, enda kune embed tab
• Uncheck the current time range - tichaseta nguva neURL
• Sarudza musoro unodiwa. Kana ndiri ini kwakareruka
• Kopa iyo URL inokonzeresa kune lovelace-UI kuseta kadhi

      - type: iframe
        id: graf_water_hourly
        url: "http://192.168.10.200:3000/d-solo/rZARemQWk/water?orgId=1&panelId=2&from=now-2d&to=now&theme=light"

Ndokumbira utarise kuti nguva (mazuva ekupedzisira e2) yakaiswa pano, uye kwete mumasetin'i edashboard.

Girafu rinotaridzika seizvi. Handina kushandisa mvura inopisa mumazuva ekupedzisira e2, saka chete girafu yemvura inotonhora inotorwa.

Ini handisati ndazvisarudzira ini girafu randinoda zvirinani, mutsara-nhanho, kana mabhawa chaiwo. Naizvozvo, ini ndinongopa muenzaniso wechati yekushandisa zuva nezuva, chete panguva ino mumabhawa. Zvikumbiro zvinogadzirwa zvakafanana kune izvo zvataurwa pamusoro apa. Zvisarudzo zvekuratidzira ndezvi:

Iyi girafu inoita seizvi:

Saka nezve iyo Stack parameter. Mugirafu iyi, mbiru yemvura inotonhora inodhirowewa pamusoro pembiru yemvura inopisa. Kureba kwese kunoenderana nekushandiswa kwese kwemvura inotonhora uye inopisa yenguva.

Magirafu ese anoratidzwa ane simba. Iwe unogona kutenderedza mbeva yako pamusoro penzvimbo yekufarira uye woona iwo maficha uye kukosha pane yakatarwa nzvimbo.

Sezvineiwo, paive nenhunzi mbiri mumafuta ekuzora. Pabhari chati (kusiyana nechati ine mitsara yematanho), pakati pebhari haisi pakati pezuva, asi pa00:00. Avo. hafu yekuruboshwe yekoramu inodhirowewa panzvimbo yezuva rapfuura. Saka magirafu eMugovera neSvondo anokweverwa zvishoma kuruboshwe rwebluish zone. Kusvikira ndafunga kuti ndozvikunda sei.

Rimwe dambudziko nderekutadza kushanda zvakanaka pamwedzi wega wega. Ichokwadi ndechokuti urefu hweawa / zuva / vhiki hunogadziriswa, asi urefu hwemwedzi hwakasiyana nguva imwe neimwe. InfluxDB inogona kungoshanda panguva dzakaenzana. Parizvino pfungwa dzangu dzave dzakakwana kuseta nguva yakatarwa yemazuva makumi matatu. Ehe, girafu richayangarara zvishoma mukati megore uye mabara haazonyatsoenderana nemwedzi. Asi sezvo ini ndichifarira chinhu ichi senge mita yekuratidzira, ini ndakanaka nazvo.

Ndinoona mhinduro mbiri:

• Rega pamachati emwedzi uye uzviganhurire kune evhiki nevhiki. 52 mabhawa evhiki nevhiki egore anotaridzika zvakanaka
• Funga kushandiswa kwemwedzi pachako senzira Nhamba 2, uye shandisa grafana chete kune magirafu akanaka. Ichava mhinduro chaiyo. Iwe unogona kunyange kufukidza magirafu egore rapfuura kuenzanisa - grafana anogona kuita izvozvo zvakare.

mhedziso

Handizive kuti sei, asi ndinobatikana nemhando idzi dzemagirafu. Vanoratidza kuti hupenyu hwave kuita uye zvese zviri kuchinja. Nezuro pane zvakawanda, nhasi pane zvishoma, mangwana zvichave zvimwewo. Chinosara ndechekushanda nenhengo dzemhuri pane nyaya yekushandisa. Asi kunyangwe nezvido zvemazuva ano, chiverengero chakakura uye chisinga nzwisisike pane slip yekubhadhara chave kutoshanduka kuita mufananidzo unonzwisisika wekushandisa.

Pasinei nebasa rangu remakore makumi maviri semugadziri wepurogiramu, ndakanga ndisati ndamboonana nemadhatabhesi. Naizvozvo, kuisa dhatabhesi rekunze kwaiita sechinhu chisinganzwisisiki uye chisinganzwisisike. Zvakachinja zvese pamusoro chinyorwa - zvakazoitika kuti kubatanidza chishandiso chakakodzera kunoitwa mukudzvanya kwakati, uye nechishandiso chakasarudzika, basa rekugadzira machati rinova nyore zvishoma.

Mumusoro ndakataura kushandiswa kwemagetsi. Nehurombo, parizvino handikwanise kupa chero magirafu. Imwe SDM120 mita yakafira ini, uye imwe yacho haina kujeka kana yasvika kuburikidza neModbus. Zvisinei, izvi hazvikanganisi musoro wechinyorwa ichi nenzira ipi zvayo - magirafu achavakwa nenzira yakafanana neyemvura.

Muchikamu chino ndakaratidza nzira dzandakaedza ini. Chokwadi kune dzimwe nzira dzekuronga kuunganidza data uye kuona kwandisingazive nezvazvo. Ndiudze nezvazvo mumashoko, ini ndichafarira zvikuru. Ndichafara kutsoropodza kunovaka uye pfungwa itsva. Ndinovimba kuti zvinyorwa zvinoratidzwa zvichabatsirawo mumwe munhu.

Source: www.habr.com