Ikhaya elihlakaniphile: Sakha amagrafu okusetshenziswa kwamanzi nogesi Kumsizi Wasekhaya

Njalo uma ngithola inkokhelo kagesi namanzi, ngiyamangala - ingabe umndeni wami udla kakhulu kangaka? Yebo, yebo, indlu yokugezela inephansi elishisayo kanye ne-boiler, kodwa abashisi imililo ngaso sonke isikhathi. Siphinde sibonakale siwonga amanzi (yize sithanda nokuchaphaza endlini yangasese). Eminyakeni eminingana edlule mina kakade amamitha amanzi axhunyiwe и ugesi ekhaya elihlakaniphile, kodwa yilapho izinto zibambeke khona. Manje sesifinyelele kuphela ekuhlaziyeni ukusetshenziswa, okuyilokho lesi sihloko esimayelana nakho.

Ngisanda kushintshela Kumsizi Wasekhaya njengohlelo lwami lwasekhaya oluhlakaniphile. Esinye sezizathu kwaba ithuba lokuhlela ukuqoqwa kwenani elikhulu ledatha enekhono lokwakha kalula izinhlobo ezahlukene zamagrafu.

Imininingwane echazwe kulesi sihloko ayiyona entsha; zonke lezi zinto ngaphansi kwamasoso ahlukene sezichazwe ku-inthanethi. Kodwa isihloko ngasinye sivame ukuchaza indlela eyodwa kuphela noma isici. Kwadingeka ngiqhathanise zonke lezi zindlela futhi ngikhethe engifanele kakhulu mina. I-athikili namanje ayinikezi ulwazi olubanzi mayelana nokuqoqwa kwedatha, kodwa iwuhlobo lwesifinyezo sendlela engikwenze ngayo. Ngakho-ke ukugxeka okwakhayo neziphakamiso zokuthuthukisa zamukelekile.

Ukwakheka kwenkinga

Ngakho-ke, inhloso yokuzivocavoca kwanamuhla ukuthola amagrafu amahle okusetshenziswa kwamanzi nogesi:

• Ngehora izinsuku ezi-2
• Nsuku zonke amaviki ama-2
• (uma uthanda) masonto onke nanyanga zonke

Kunobunzima obuthile ngalokhu:

• Izingxenye zeshadi ezijwayelekile ngokuvamile ziba mbi kakhulu. Okungcono kakhulu, ungakha iphoyinti legrafu yomugqa ngephoyinti.

  Uma ubukeka uqine ngokwanele, ungathola izingxenye zenkampani yangaphandle ezinweba amandla eshadi elivamile. Kumsizi wasekhaya, ngokomthetho, lokhu kuyisici esihle futhi esihle mini-graph ikhadi, kodwa futhi inomkhawulo ngandlela-thile:

  • Kunzima ukusetha amapharamitha eshadi lebha ngezikhawu ezinkulu (ububanzi bebha busethwe ngezingxenyana zehora, okusho ukuthi izikhawu ezinde kunehora zizosethwa ngezinombolo eziyingxenye)
  • Awukwazi ukwengeza amabhizinisi ahlukene kugrafu eyodwa (isibonelo, izinga lokushisa nokuswakama, noma ukuhlanganisa igrafu yebha nomugqa)
• Umsizi wasekhaya akasebenzisi nje ngokuzenzakalelayo isizindalwazi sakudala se-SQLite (futhi mina, umsizi, angikwazanga ukusingatha ukufaka i-MySQL noma i-Postgres), kodwa idatha ayigcinwa ngendlela efanele kakhulu. Ngakho-ke, ngokwesibonelo, ngaso sonke isikhathi lapho ushintsha ngisho nepharamitha yedijithali encane kunazo zonke yepharamitha, i-json enkulu cishe ngekhilobhayithi ngosayizi ibhalwa kusizindalwazi.

  {"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}}}

  Nginezinzwa eziningi (izinzwa lokushisa egumbini ngalinye, amamitha amanzi nogesi), kanti ezinye zikhiqiza idatha eningi impela. Isibonelo, imitha kagesi ye-SDM220 iyodwa ikhiqiza amanani acishe abe yishumi nambili njalo ngemizuzwana eyi-10-15, futhi ngingathanda ukufaka amamitha anjalo angama-8. Kukhona nenqwaba yamapharamitha abalwa ngokususelwa kwezinye izinzwa. Lokho. wonke lawa manani angakhuphula kalula i-database ngo-100-200 MB nsuku zonke. Ngesonto uhlelo luzohamba kancane, futhi ngenyanga i-flash drive izofa (uma kwenzeka ukufakwa komsizi wasekhaya ojwayelekile ku-Raspberry PI), nokugcina idatha unyaka wonke akubuzwa.

• Uma unenhlanhla, imitha yakho ingabala ukusetshenziswa kwayo. Ungaphendukela kumitha nganoma yisiphi isikhathi futhi ubuze ukuthi inani lokusetshenziswa eliqoqiwe linini. Njengomthetho, wonke amamitha kagesi ane-interface yedijithali (RS232/RS485/Modbus/Zigbee) anikeza leli thuba.

  Kubi kakhulu uma idivayisi ingakwazi ukukala ipharamitha esheshayo (isibonelo, amandla asheshayo noma isimanje), noma ivele ikhiqize ama-pulses njalo ngamahora angu-X watt noma amalitha. Khona-ke udinga ukucabanga ukuthi ungayihlanganisa kanjani nokuthi ungayihlanganisa kanjani nokuthi ungaqongelela kuphi inani. Kukhona ingozi yokulahlekelwa umbiko olandelayo nganoma yisiphi isizathu, futhi ukunemba kwesistimu iyonke kuphakamisa imibuzo. Yebo, ungakuphathisa konke lokhu ohlelweni lwekhaya oluhlakaniphile njengomsizi wasekhaya, kodwa akekho oye wakhansela iphuzu mayelana nenani lamarekhodi ku-database, futhi ngeke kwenzeke ukuphenya izinzwa izikhathi ezingaphezu kwesisodwa ngomzuzwana (a umkhawulo wesakhiwo somsizi wekhaya).

Indlela 1

Okokuqala, ake sibone ukuthi umsizi wasekhaya uhlinzeka ngani ngaphandle kwebhokisi. Ukulinganisa ukusetshenziswa kwesikhathi esithile kuwumsebenzi ofunwa kakhulu. Yiqiniso, yasetshenziswa kudala ngendlela yengxenye ekhethekile - utility_meter.

Ingqikithi yengxenye iwukuthi idala ngaphakathi inani eliguquguqukayo lamanje_eliqoqiwe_ibese isetha kabusha ngemva kwesikhathi esithile (ihora/isonto/inyanga). Ingxenye ngokwayo iqapha ukuguquguquka kokufaka (inani lenzwa ethile), ibhalisela izinguquko enanini - uthola umphumela ophelile. Le nto ichazwa emigqeni embalwa nje kufayela lokumisa

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

Lapha i-sensor.water_meter_cold inani lamanje lemitha ngamalitha engiwatholayo ngokuqondile kusukela ocezwini lwensimbi nge mqt. Idizayini idala izinzwa ezintsha ezingu-2 i-water_cold_hour_um kanye ne-water_cold_day_um, eziqongelela ukufundwa kwehora nosuku nosuku, ziwabuyisele kuqanda ngemva kokuphelelwa yisikhathi. Nali igrafu yebhethri yehora isigamu sosuku.

Ikhodi yamashadi ehora nansuku zonke ye-lovelace-UI ibukeka kanje:

      - 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

Eqinisweni, inkinga ngale ndlela ikule algorithm. Njengoba ngike ngasho, ngevelu yokufaka ngayinye (ukufundwa kwemitha yamanje kulitha ngalinye elilandelayo) 1kb wamarekhodi akhiqizwa kusizindalwazi. Imitha ngayinye yokusetshenziswa nayo ikhiqiza inani elisha, elibuye lifakwe esisekelweni. Uma ngifuna ukuqoqa ukufundwa kwehora / kwansuku zonke / kweviki / kwenyanga, kanye nezinyusi ezimbalwa zamanzi, futhi ngengeze iphakethe lamamitha kagesi, lokho kuzoba idatha eningi. Yebo, ngokunembile, ayikho idatha eningi, kodwa njengoba umsizi wekhaya ebhala inqwaba yolwazi olungadingekile ku-database, ubukhulu bedathabhesi buzokhula ngokugxuma nemingcele. Ngesaba ngisho nokulinganisa usayizi wesisekelo samashadi angesonto nanyanga zonke.

Ngaphezu kwalokho, imitha yokusetshenziswa ngokwayo ayixazululi inkinga. Igrafu yamanani akhiqizwe imitha yosizo iwumsebenzi okhuphuka kancane osetha kabusha ube ngu-0 njalo ngehora. Sidinga ishadi lokusetshenziswa eliqondakalayo kumsebenzisi, elibonisa ukuthi mangaki amalitha asetshenzisiwe phakathi naleso sikhathi. Ingxenye evamile yegrafu yomlando ayikwazi ukwenza lokhu, kodwa ingxenye yangaphandle ye-mini-graph-card ingasisiza.

Lena ikhodi yekhadi ye-lovelace-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'

Ngokungeziwe kuzilungiselelo ezijwayelekile njengegama lenzwa, uhlobo lwegrafu, umbala (angizange ngikuthande okusawolintshi okujwayelekile), kubalulekile ukuqaphela izilungiselelo ezi-3:

• group_by:hora — igrafu izokwenziwa ngemigoqo eqondaniswe ekuqaleni kwehora
• amaphuzu_ngehora_ngehora: 1 - ibha eyodwa ihora nehora
• Futhi okubaluleke kakhulu, i-aggregate_func: max - thatha inani eliphezulu phakathi nehora ngalinye. Yile parameter ephendula igrafu ye-sawtooth ibe imigoqo

Unganaki umugqa wamakholomu kwesokunxele - lokhu ukuziphatha okujwayelekile kwengxenye uma ingekho idatha. Kodwa ibingekho idatha - ngivule kuphela ukuqoqwa kwedatha yemitha yokusetshenziswa emahoreni ambalwa edlule ngenxa yalesi sihloko (ngizochaza indlela yami yamanje ngezansi).

Kulesi sithombe bengifuna ukukhombisa ukuthi kwesinye isikhathi isibonisi sedatha siyasebenza futhi imigoqo ikhombisa amanani alungile. Kodwa akugcini lapho. Ngesizathu esithile, ikholomu ekhethiwe yenkathi kusukela ngo-11 kuya ku-12 ekuseni ibonisa amalitha angu-19, nakuba kugrafu ye-toothy ephakeme kancane ngesikhathi esifanayo kusukela kunzwa efanayo sibona ukusetshenziswa kwamalitha angu-62. Kuphakathi kokuthi kunesiphazamisi noma izandla zigwegwile. Kodwa namanje angiqondi ukuthi kungani idatha engakwesokudla inqamukile - ukusetshenziswa lapho bekujwayelekile, futhi kubonakala kugrafu yamazinyo.

Ngokuvamile, angikwazanga ukufeza ukufaneleka kwale ndlela - igrafu cishe njalo ibonisa uhlobo oluthile lokuhlubuka.

Ikhodi efanayo yenzwa yasemini.

      - 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'

Sicela uqaphele ukuthi ipharamitha yeqembu_by isethelwe kusikhawu, futhi ipharamitha yamaphoyinti_ngehora ilawula yonke into. Futhi lapho kukhona enye inkinga ngale ngxenye - points_per_hour isebenza kahle kumashadi ehora noma ngaphansi, kodwa idonsa izikhawu ezinkulu. Ngakho-ke ukuze ngithole ikholomu eyodwa ngosuku olulodwa, bekufanele ngifake inani 1/24=0.04166666. Angikhulumi ngisho ngamashadi amasonto onke nawenyanga.

Indlela 2

Ngenkathi ngisaqonda umsizi wasekhaya, ngihlangabezane nale vidiyo:

Umngane uqoqa idatha yokusetshenziswa ezinhlotsheni ezimbalwa zamasokhethi e-Xiaomi. Umsebenzi wakhe ulula kancane - mane ubonise inani lokusetshenziswa kwanamuhla, izolo kanye nenyanga. Awekho amashejuli adingekayo.

Ake sishiye eceleni izingxoxo mayelana nokuhlanganiswa ngesandla kwamanani wamandla asheshayo - kakade ngibhale ngenhla mayelana "nokunemba" kwale ndlela. Akucaci ukuthi kungani engazange asebenzise amanani okusetshenziswa aqoqiwe, asevele eqoqwe yi-outlet efanayo. Ngokubona kwami, ukuhlanganiswa ngaphakathi kwehadiwe kuzosebenza kangcono.

Kuvidiyo sizothatha umqondo wokubala ukusetshenziswa kwesikhathi eside. Umfana ubala amanani wanamuhla nayizolo kuphela, kodwa sizoqhubekela phambili futhi sizame ukudweba igrafu. Ingqikithi yendlela ehlongozwayo endabeni yami imi kanje.

Masidale inani eliguquguqukayo_at_the_beginning_of_hour, lapho sizorekhoda khona ukufundwa kwamamitha wamanje
Sisebenzisa isibali sikhathi, ekupheleni kwehora (noma ekuqaleni kwelilandelayo) sibala umehluko phakathi kokufundwa kwamanje nalokhu okugcinwe ekuqaleni kwehora. Lo mehluko uzoba ukusetshenziswa kwehora lamanje - sizogcina inani kwinzwa, futhi esikhathini esizayo sizokwakha igrafu ngokusekelwe kuleli xabiso.
Udinga futhi “ukusetha kabusha” okuguquguqukayo kwe-value_at_beginning_of_hour ngokubhala inani lamanje lesibali lapho.

Konke lokhu kungenziwa ngomsizi wasekhaya uqobo.

Kuzodingeka ukuthi ubhale ikhodi ethe xaxa kunalena ebesiyisebenzisile. Okokuqala, ake sidale lezi "eziguquguqukayo" ezifanayo. Ngaphandle kwebhokisi asinalo ibhizinisi "eliguquguqukayo", kodwa singasebenzisa izinsizakalo zomthengisi we-mqtt. Sizothumela amanani lapho ne-retain=ifulegi leqiniso - lokhu kuzolondoloza inani ngaphakathi komdayisi, futhi kungakhishwa lapho noma nini, ngisho nalapho umsizi wasekhaya eqalwa kabusha. Ngenza izibali zehora nezansuku zonke ngesikhathi esisodwa.

- 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

Yonke imilingo yenzeka kumshini ozishintshayo, osebenza njalo ngehora nobusuku bonke ngokulandelana.

- 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

Kokubili okuzenzakalelayo kwenza izenzo ezi-2:

• Bala inani lesikhawu njengomehluko phakathi kwamanani okuqala nawokugcina
• Buyekeza inani eliyisisekelo lesikhathi esilandelayo

Ukwakhiwa kwamagrafu kulokhu kuxazululwa yigrafu yomlando evamile:

      - 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

Kubukeka kanjena:

Empeleni, lokhu kakade yilokho okudingekayo. Inzuzo yale ndlela ukuthi idatha ikhiqizwa kanye ngesikhawu ngasinye. Labo. amarekhodi angama-24 kuphela ngosuku eshadi lehora.

Ngeshwa, lokhu namanje akuyixazululi inkinga evamile yesisekelo esikhulayo. Uma ngifuna igrafu yokusebenzisa yanyanga zonke, kuzodingeka ngigcine idatha okungenani unyaka. Futhi njengoba umsizi wasekhaya enikeza isilungiselelo esisodwa kuphela sobude besikhathi sokulondoloza kuyo yonke imininingwane egciniwe, lokhu kusho ukuthi YONKE idatha ohlelweni kuzodingeka igcinwe unyaka wonke. Isibonelo, ngonyaka ngidla ama-cubic metres angu-200 wamanzi, okusho ukuthi lokhu kusho okufakiwe kwe-200000 ku-database. Futhi uma ucabangela ezinye izinzwa, isibalo siba ngokungafaneleki.

Indlela 3

Ngenhlanhla, abantu abahlakaniphile sebeyixazululile le nkinga ngokubhala i-InfluxDB database. Le database ilungiselelwe ngokukhethekile ukugcina idatha esekwe esikhathini futhi ilungele ukugcina amanani ezinzwa ezahlukahlukene. Uhlelo futhi luhlinzeka ngolimi lwemibuzo olufana ne-SQL olukuvumela ukuthi ukhiphe amanani kusizindalwazi bese uwahlanganisa ngezindlela ezihlukahlukene. Okokugcina, idatha ehlukene ingagcinwa izikhathi ezahlukene. Isibonelo, ukufundwa okushintshashintsha njalo njengezinga lokushisa noma umswakama kungagcinwa amaviki ambalwa nje, kuyilapho ukufundwa kokusetshenziswa kwamanzi nsuku zonke kungagcinwa unyaka wonke.

Ngaphandle kwe-InfluxDB, abantu abahlakaniphile baphinde basungula i-Grafana, uhlelo lokudweba amagrafu ngokusekelwe kudatha evela ku-InfluxDB. I-Grafana ingakwazi ukudweba izinhlobo ezahlukene zamagrafu, izenze ngokwezifiso ngokuningiliziwe, futhi, okubaluleke kakhulu, lawa magrafu "angaxhunywa" kumsizi wasekhaya we-lovelace-UI.

Thola ugqozi lapha и lapha. Izindatshana zichaza ngokuningiliziwe inqubo yokufaka nokuxhuma i-InfluxDB ne-Grafana kumsizi wasekhaya. Ngizogxila ekuxazululeni inkinga yami ethile.

Ngakho-ke, okokuqala, ake siqale ukungeza inani eliphikisayo ku-influxDB. Ucezu lokucushwa komsizi wasekhaya (kulesi sibonelo ngizojabula hhayi ngokubanda kuphela, kodwa namanzi ashisayo):

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

Masikhubaze ukulondoloza le datha efanayo kusizindalwazi somsizi wekhaya langaphakathi ukuze singaphinde siyivimbe futhi:

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

Manje ake siye kukhonsoli ye-InfluxDB futhi silungise imininingwane yethu. Ikakhulukazi, udinga ukulungisa ukuthi idatha ethile izogcinwa isikhathi esingakanani. Lokhu kulawulwa yilokhu okubizwa. inqubomgomo yokugcina - lokhu kufana nezingobo zolwazi ezingaphakathi kwesizindalwazi esikhulu, lapho ukugcinwa kwemininingwane yangaphakathi ngayinye inezilungiselelo zayo. Ngokuzenzakalelayo, yonke idatha igcinwa kunqubomgomo yokugcinwa ebizwa ngokuthi i-autogen; le datha izogcinwa iviki lonke. Ngingathanda ukuthi idatha yehora igcinwe inyanga, idatha yangeviki igcinwe unyaka, kanye nedatha yanyanga zonke ingalokothi isuswe. Masidale inqubomgomo yokugcinwa efanele

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

Manje, empeleni, iqhinga eliyinhloko ukuhlanganisa idatha kusetshenziswa umbuzo oqhubekayo. Lena indlela eqhuba ngokuzenzakalelayo umbuzo ngezikhathi ezithile, ihlanganisa idatha yalo mbuzo, bese yengeza umphumela enanini elisha. Ake sibheke isibonelo (ngibhala kukholamu ukuze ifundeke, kodwa empeleni bekufanele ngifake lo myalo emugqeni owodwa)

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

Lo myalo:

• Idala umbuzo oqhubekayo oqanjwe cq_water_cold_hourly kusizindalwazi somsizi wasekhaya
• Isicelo sizokwenziwa njalo ngehora (isikhathi(1h))
• Isicelo sizokwesula yonke idatha kusuka kusilinganiso' homeassistant.autogen.l (amalitha), okuhlanganisa ukufundwa kwamanzi abandayo nashisayo
• Idatha ehlanganisiwe izoqoqwa nge-entity_id, ezosinika amanani ahlukene amanzi abandayo nashisayo
• Njengoba ikhawunta yelitha iwukulandelana okwandayo okukodwa phakathi kwehora ngalinye, kuzodingeka ukuthi kuthathwe inani eliphakeme, ngakho ukuhlanganisa kuzokwenziwa ubuningi bomsebenzi(inani)
• Inani elisha lizobhalelwa ku-homeassistant.month.water_meter_hour, lapho inyanga iyigama lenqubomgomo yokugcinwa enesikhathi sokugcinwa esiyinyanga eyodwa. Ngaphezu kwalokho, idatha emanzini abandayo nashisayo izosatshalaliswa ibe amarekhodi ahlukene anebhizinisi_id elihambisanayo kanye nenani lenkambu yenani.

Ebusuku noma lapho kungekho muntu ekhaya, amanzi awasetshenziswa, ngakho-ke akukho okusha okufakiwe kokuthi homeassistant.autogen.l. Ukuze ugweme amanani alahlekile emibuzweni evamile, ungasebenzisa ukugcwalisa (okwangaphambilini). Lokhu kuzophoqa i-InfluxDB ukuthi isebenzise inani lehora lokugcina.

Ngeshwa, umbuzo oqhubekayo unezinto ezingavamile: iqhinga lokugcwalisa(elidlule) alisebenzi futhi amarekhodi awadalwanga. Ngaphezu kwalokho, lolu uhlobo oluthile lwenkinga engenakunqotshwa ukuthi sekukhulunywe ngakho iminyaka eminingana manje. Sizobhekana nale nkinga ngokuhamba kwesikhathi, kodwa ake ukugcwalisa(okwangaphambilini) kube sembuzweni oqhubekayo - akuphazamisi.

Ake sihlole ukuthi kwenzekeni (Yebo, udinga ukulinda amahora ambalwa):

> 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

Qaphela ukuthi amanani kusizindalwazi agcinwe ku-UTC, ngakho lolu hlu luhluka ngamahora angu-3 - amanani angu-7am ekuphumeni kwe-InfluxDB ahambisana namanani angu-10am kumagrafu angenhla. Futhi qaphela ukuthi phakathi kuka-2 no-5 am awekho amarekhodi - lesi isici esifanayo sombuzo oqhubekayo.

Njengoba ubona, inani elihlanganisiwe libuye libe ukulandelana okwandayo okukodwa, okungenayo kuphela okwenzeka kancane - kanye ngehora. Kodwa lokhu akuyona inkinga - singabhala omunye umbuzo ozobuyisa idatha elungile yegrafu.

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)

Ngizochaza:

• Kusizindalwazi se-homeassistant.month.water_meter_hour sizokhipha idatha ye-entity_id='water_meter_cold' yosuku lokugcina (isikhathi >= manje() -24h).
• Njengoba sengishilo, okunye okufakiwe kungenzeka ukuthi akukho ekulandeleni kwe-homeassistant.month.water_meter_hour. Sizokhiqiza kabusha le datha ngokuqalisa umbuzo nge-GROUP BY isikhathi(1h). Lesi sikhathi sokugcwalisa(esidlule) sizosebenza njengoba kulindelekile, sikhiqize idatha engekho (umsebenzi uzothatha inani langaphambilini)
• Into ebaluleke kakhulu kulesi sicelo umsebenzi womehluko, ozobala umehluko phakathi kwamamaki ehora. Ayisebenzi yodwa futhi idinga umsebenzi wokuhlanganisa. Ake lokhu kube yi-max() esetshenziswe ngaphambilini.

Umphumela wokwenza ubukeka kanje

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

Kusukela ngo-2 kuya ku-5 am (UTC) kwakungekho ukusetshenziswa. Noma kunjalo, umbuzo uzobuyisela inani elifanayo lokusetshenziswa ngenxa yokugcwalisa(okwangaphambilini), futhi umsebenzi womehluko uzokhipha leli nani kuwo futhi okukhiphayo kuzoba ngu-0, okuyilokho kanye okudingekayo.

Okusele wukwakha igrafu. Ukuze wenze lokhu, vula i-Grafana, vula ideshibhodi ekhona (noma dala entsha), bese udala iphaneli entsha. Izilungiselelo zeshadi zizoba kanje.

Ngizobonisa idatha yamanzi abandayo nashisayo kugrafu efanayo. Isicelo sifana ncamashi naleso engisichaze ngenhla.

Imingcele yokubonisa isethwe kanje. Kimina kuzoba igrafu enemigqa, ehamba ngezinyathelo (izitebhisi). Ngizochaza ipharamitha yesitaki ngezansi. Kunezinketho ezimbalwa zokubonisa ngaphezulu ngezansi, kodwa azithakazelisi kangako.

Ukwengeza ishadi eliwumphumela kumsizi wasekhaya udinga:

• phuma kumodi yokuhlela ishadi. Ngesizathu esithile, izilungiselelo ezilungile zokwabelana ngeshadi zinikezwa kuphela ekhasini ledeshibhodi
• Chofoza unxantathu eduze kwegama leshadi bese ukhetha ukwabelana kusuka kumenyu
• Ewindini elivulayo, hamba kuthebhu yokushumeka
• Susa ukumaka ibanga lesikhathi samanje - sizosetha ububanzi besikhathi nge-URL
• Khetha isihloko esidingekayo. Kwami kukhanya
• Kopisha i-URL ewumphumela ekhadini lezilungiselelo ze-lovelace-UI

      - 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"

Sicela uqaphele ukuthi ibanga lesikhathi (izinsuku ezingu-2 zokugcina) libekwe lapha, hhayi kuzilungiselelo zedeshibhodi.

Igrafu ibukeka kanje. Angizange ngisebenzise amanzi ashisayo ezinsukwini ezingu-2 zokugcina, ngakho-ke igrafu yamanzi abandayo kuphela edwetshwayo.

Angikazinqumeli mina ngokwami ​​ukuthi iyiphi igrafu engiyithanda kangcono, isinyathelo somugqa, noma amabha angempela. Ngakho-ke, ngizomane nginikeze isibonelo seshadi lokusetshenziswa kwansuku zonke, kulokhu kuphela kumabha. Imibuzo yakhiwe ngendlela efanayo naleyo echazwe ngenhla. Izinketho zokubonisa yilezi:

Le grafu ibukeka kanje:

Ngakho mayelana nepharamitha yesitaki. Kule grafu, ikholomu yamanzi abandayo idwetshwa phezulu kwekholomu yamanzi ashisayo. Ukuphakama okuphelele kuhambisana nokusetshenziswa okuphelele kwamanzi abandayo nashisayo kule nkathi.

Wonke amagrafu abonisiwe ayashintshashintsha. Ungahambisa igundane lakho phezu kwendawo oyithakaselayo futhi ubone imininingwane nenani endaweni ethile.

Ngeshwa, kwakukhona izimpukane ezimbalwa endaweni yokugcoba. Eshadini lebha (ngokungafani neshadi elinemigqa yesinyathelo), indawo ephakathi nendawo ayikho phakathi nosuku, kodwa ngo-00:00. Labo. ingxenye engakwesokunxele yekholomu idwetshwe esikhundleni sosuku lwangaphambilini. Ngakho-ke amagrafu angoMgqibelo nangeSonto adwetshwa kancane kwesokunxele sendawo eluhlaza okwesibhakabhaka. Ngize ngicabange ukuthi ngingayinqoba kanjani.

Enye inkinga ukungakwazi ukusebenza kahle njalo ngenyanga. Iqiniso liwukuthi ubude behora / usuku / isonto bunqunyiwe, kodwa ubude benyanga buhlukile isikhathi ngasinye. I-InfluxDB ingasebenza kuphela ngezikhathi ezilinganayo. Kuze kube manje ubuchopho bami bebanele ukusetha isikhawu esinqunyiwe sezinsuku ezingama-30. Yebo, igrafu izontanta kancane unyaka wonke futhi imigoqo ngeke ihambisane ncamashi nezinyanga. Kodwa njengoba nginentshisekelo kule nto njengemitha yokubonisa, ngilungile ngayo.

Ngibona okungenani izixazululo ezimbili:

• Yekani ngamashadi anyanga zonke futhi ubeke umkhawulo kumaviki onke. Amabha amasonto onke angama-52 onyaka abukeka emuhle kakhulu
• Cabangela ukusetshenziswa kwanyanga zonke ngokwakho njengendlela No. 2, futhi usebenzise i-grafana kumagrafu amahle kuphela. Kuyoba isixazululo esinembile impela. Ungakwazi ngisho nokumbondela amagrafu onyaka odlule ukuze uqhathanise - i-grafana nayo ingakwenza lokho.

isiphetho

Angazi ukuthi kungani, kodwa ngiyazifela ngalezi zinhlobo zamagrafu. Bakhombisa ukuthi impilo iyashintsha futhi yonke into iyashintsha. Izolo bekuningi, namuhla kuncane, kusasa kuzoba ngokunye. Okusele wukusebenza namalungu omndeni esihlokweni sokudliwa. Kodwa ngisho nangezifiso zamanje, isibalo esikhulu futhi esingaqondakali esiliphuni sokukhokha sesivele siphenduka isithombe esiqondakalayo sokusetshenziswa.

Naphezu komsebenzi wami weminyaka ecishe ube ngu-20 njengomhleli, ngangingenakho nhlobo ukuxhumana nesizindalwazi. Ngakho-ke, ukufaka i-database yangaphandle kwakubonakala njengento engaqondakali futhi engaqondakali. Kushintshe konke isihloko esingenhla - kuvele ukuthi ukunamathisela ithuluzi elifanelekile kwenziwa ngokuchofoza okumbalwa, futhi ngethuluzi elikhethekile, umsebenzi wokuhlela amashadi uba lula kancane.

Esihlokweni ngibalule ukusetshenziswa kukagesi. Ngeshwa, okwamanje angikwazi ukunikeza noma yimaphi amagrafu. Imitha eyodwa ye-SDM120 ingifele, kanti enye ayilungile uma ifinyelelwa nge-Modbus. Noma kunjalo, lokhu akuthinti isihloko salesi sihloko nganoma iyiphi indlela - amagrafu azokwakhiwa ngendlela efanayo namanzi.

Kulesi sihloko ngethule izindlela engizame ngazo mina. Impela zikhona ezinye izindlela zokuhlela ukuqoqwa kwedatha nokubonwa ngamehlo engingazi ngakho. Ngitshele ngakho kumazwana, ngizoba nesithakazelo kakhulu. Ngizokujabulela ukugxeka okwakhayo kanye nemibono emisha. Ngethemba ukuthi izinto ezizokwethulwa nazo zizosiza othile.

Source: www.habr.com