Ikhaya elihlakaniphile: Sakha iigrafu zokusetyenziswa kwamanzi kunye nombane kuMncedisi weKhaya

Ngalo lonke ixesha ndifumana intlawulo yombane kunye namanzi, ndiyamangala - ngaba usapho lwam lutya kakhulu kangaka? Ewe, ewe, igumbi lokuhlambela linomgangatho ofudumeleyo kunye nebhoyili, kodwa abatshisi imililo ngamaxesha onke. Sikwabonakala ngathi siyawonga amanzi (nangona sikwathanda ukutshiza apha kwigumbi lokuhlambela). Kwiminyaka eliqela eyadlulayo sele iimitha zamanzi ezidityanisiweyo и umbane kwikhaya elikrelekrele, kodwa kulapho izinto zaxinga khona. Ngoku sele sijikeleze ukuhlalutya ukusetyenziswa, eyona nto eli nqaku limalunga nayo.

Kutshanje nditshintshele kuMncedisi weKhaya njengenkqubo yam yasekhaya ekrelekrele. Esinye sezizathu yayilithuba ngokuchanekileyo lokuququzelela ukuqokelela inani elikhulu ledatha kunye nokukwazi ukwakha ngokufanelekileyo iintlobo ezahlukeneyo zeegrafu.

Ulwazi oluchazwe kweli nqaku alukho lutsha; zonke ezi zinto phantsi kweesosi ezahlukeneyo sele zichazwe kwi-Intanethi. Kodwa inqaku ngalinye lidla ngokuchaza indlela enye okanye inkalo enye kuphela. Kwafuneka ndithelekise zonke ezi ndlela kwaye ndikhethe eyona ifanelekileyo ngokwam. Eli nqaku alikaboneleli ngolwazi olubanzi malunga nokuqokelela idatha, kodwa luhlobo lwesishwankathelo sendlela endenze ngayo. Ngoko ukugxeka okwakhayo kunye neengcebiso zokuphucula zamkelekile.

Џџ ѕЃ ° °

Ke, injongo yoqeqesho lwanamhlanje kukufumana iigrafu ezintle zamanzi kunye nokusetyenziswa kombane:

• Ngeyure iintsuku ezi-2
• Yonke imihla kwiiveki ezi-2
• (ukhetho) ngeveki nangenyanga

Kukho ubunzima malunga noku:

• Amalungu etshathi esemgangathweni adla ngokuba mbi kakhulu. Okona kulungileyo, unokwakha inqaku legrafu yomgca ngenqaku.

  Ukuba ujonga nzima ngokwaneleyo, unokufumana amacandelo eqela lesithathu elandisa ubunakho betshathi eqhelekileyo. Kumncedisi wekhaya, ngokomgaqo, eli licandelo elihle kwaye elihle ikhadi legrafu encinci, kodwa ikwanomda noko:

  • Kunzima ukuseta iiparameters zetshathi yebha kumathuba amakhulu (ububanzi bebar bubekwe ngamaqhezu eyure, oku kuthetha ukuba izithuba ezinde kuneyure ziya kusekwa ngamanani aqhekekileyo)
  • Awukwazi ukongeza izinto ezahlukeneyo kwigrafu enye (umzekelo, ubushushu kunye nokufuma, okanye udibanise igrafu yebar ngomgca)
• Umncedisi wasekhaya akasebenzisi kuphela isiseko sedatha yeSQLite yamandulo (kwaye mna, i-handyman, andinakukwazi ukusingatha ukufaka i-MySQL okanye i-Postgres), kodwa idatha ayigcinwa ngeyona ndlela ifanelekileyo. Ngoko, umzekelo, ngalo lonke ixesha utshintsha neyona incinci iparameter yedijithali yeparameter, ijson enkulu malunga ne kilobyte ngobukhulu ibhalwa kwisiseko sedatha.

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

  Ndinezivamvo ezininzi (izinzwa zobushushu kwigumbi ngalinye, iimitha zamanzi kunye nombane), kwaye ezinye zivelisa idatha eninzi kakhulu. Ngokomzekelo, imitha yombane ye-SDM220 yodwa ivelisa amaxabiso amalunga neshumi elinesibini rhoqo ngemizuzwana eyi-10-15, kwaye ndingathanda ukufaka malunga neemitha ezi-8. Kukho neqela elipheleleyo leeparitha ezibalwe ngokusekelwe kwezinye izinzwa. Oko. zonke ezi xabiso zinokunyusa ngokulula i-database nge-100-200 MB yonke imihla. Ngeveki inkqubo ayiyi kuhamba ngokukhawuleza, kwaye ngenyanga i-flash drive iya kufa (kwimeko yokufakelwa komncedisi wekhaya oqhelekileyo kwi-Raspberry PI), kunye nokugcina idatha unyaka wonke ngaphandle kombuzo.

• Ukuba unethamsanqa, isilinganisi-manzi sakho singabala ukusetyenziswa ngokwaso. Ungajika kwimitha nangaliphi na ixesha kwaye ubuze ukuba leliphi ixesha eliqokelelweyo lexabiso lokusetyenziswa. Njengomthetho, zonke iimitha zombane ezine-digital interface (RS232/RS485/Modbus/Zigbee) zibonelela ngeli thuba.

  Kubi kakhulu ukuba isixhobo sinokumeta ngokulula ipharamitha ekhawulezileyo (umzekelo, amandla akhawulezileyo okanye angoku), okanye ngokulula ukuvelisa iipulse rhoqo nge-X watt-yure okanye iilitha. Emva koko kufuneka ucinge malunga nendlela kunye nento onokuyidibanisa kunye nendawo yokuqokelela ixabiso. Kukho umngcipheko wokulahlekelwa ingxelo elandelayo nangasiphi na isizathu, kwaye ukuchaneka kwenkqubo yonke kuphakamisa imibuzo. Unako, ewe, ukuphathisa konke oku kwinkqubo yekhaya elumkileyo njengomncedisi wasekhaya, kodwa akukho mntu uye wayicima inqaku malunga nenani leerekhodi ezikwisiseko sedatha, kwaye akunakukwazi ukwenza ii-poll sensors ngaphezu kwesihlandlo esinye ngomzuzwana (a. umda woyilo lomncedisi wekhaya).

Indlela yoku-1

Okokuqala, makhe sibone ukuba umncedisi wekhaya unikezela ntoni ngaphandle kwebhokisi. Ukulinganisa usetyenziso kwithuba elithile ngumsebenzi ofunwa kakhulu. Ewe, yaphunyezwa kwakudala ngendlela yecandelo elikhethekileyo - utility_meter.

Ingundoqo yecandelo kukuba ngaphakathi idala i-variable current_accumulated_value kwaye iyibuyisele emva kwexesha elithile (iyure / iveki / inyanga). Icandelo ngokwalo lijonga ukuguquguquka kwegalelo (ixabiso lenzwa ethile), libhalisela utshintsho kwixabiso - ufumana umphumo ogqityiweyo. Le nto ichazwe kwimigca embalwa kwifayile yoqwalaselo

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

Apha sensor.water_meter_cold lixabiso lemitha yangoku kwiilitha endizifumanayo ngqo kwiqhekeza lentsimbi ngo mqtt. Uyilo ludala abenzi boluvo abatsha aba-2 water_cold_hour_um kunye ne-water_cold_day_um, eqokelela ufundo lweyure kunye nolwemihla ngemihla, lusetha kwakhona kwi-zero emva kokuba ixesha liphelile. Nantsi igrafu yebhetri yeyure ngesiqingatha sosuku.

Ikhowudi yeetshathi zeyure kunye nemihla ngemihla ye-lovelace-UI ijongeka ngolu hlobo:

      - 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

Ngokwenyani, ingxaki ngale ndlela ilele kule algorithm. Njengoko sele ndikhankanyile, kwixabiso legalelo ngalinye (ukufundwa kweemitha zangoku kwilitha nganye elandelayo) i-1kb yeerekhodi ziveliswa kwiziko ledatha. Imitha nganye esetyenziswayo iphinda ivelise ixabiso elitsha, eliphinda lifakwe kwisiseko. Ukuba ndifuna ukuqokelela iyure / imihla ngemihla / iveki / inyanga nenyanga, kunye nokunyuka kwamanzi amaninzi, kwaye ndongeze ipakethi yeemitha zombane, oko kuya kuba yidatha eninzi. Ewe, ngokuchanekileyo, akukho datha eninzi, kodwa ekubeni umncedisi wekhaya ebhala inqwaba yolwazi olungadingekile kwisiseko sedatha, ubungakanani bedatha buya kukhula ngokuxhuma kunye nemida. Ndiyoyika nokuqikelela ubungakanani besiseko seetshathi zeveki nezenyanga.

Ukongeza, imitha yoncedo ngokwayo ayizisombululi ingxaki. Igrafu yamaxabiso aveliswa imitha yosetyenziso ngumsebenzi onyuka ngokulinganayo oseta kwakhona ku-0 ngeyure nganye. Sidinga itshathi yokusetyenziswa eqondakalayo kumsebenzisi, ebonisa ukuba zingaphi iilitha ezisetyenzisiweyo ngeli xesha. Icandelo eliqhelekileyo legrafu yembali alikwazi ukwenza oku, kodwa icandelo langaphandle le-mini-graph-card lingasinceda.

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

Ukongeza kwisetingi ezisemgangathweni njengegama loluvo, uhlobo lwegrafu, umbala (andiyithandi iorenji eqhelekileyo), kubalulekile ukuqaphela izicwangciso ezi-3:

• group_by: hour — igrafu iya kuveliswa ngemivalo elungelelaniswe ekuqaleni kweyure
• points_per_yure: 1 - ibha enye ngeyure nganye
• Kwaye okona kubalulekileyo, aggregate_func: max - thatha elona xabiso liphezulu kwiyure nganye. Yile parameter ejika igrafu yesawtooth ibe yimivalo

Musa ukunyamekela umqolo wekholamu ngakwesobunxele - oku kukuziphatha okusemgangathweni kwecandelo ukuba akukho datha. Kodwa bekungekho datha- ndivule kuphela ingqokelela yedatha yokusetyenziswa kwemitha kwiiyure ezimbalwa ezidlulileyo ngenxa yeli nqaku (ndiya kuchaza indlela yam yangoku ngezantsi).

Kulo mfanekiso bendifuna ukubonisa ukuba ngamanye amaxesha umboniso wedatha usebenza kwaye imivalo ibonakalisa amaxabiso achanekileyo. Kodwa akuphelelanga apho. Ngesizathu esithile, ikholamu ekhethiweyo kwixesha elivela kwi-11 ukuya kwi-12 am ibonisa iilitha ezili-19, nangona kwigrafu ye-toothy iphezulu kancinci kwixesha elifanayo ukusuka kwinzwa efanayo sibona ukusetyenziswa kweelitha ezingama-62. Mhlawumbi kukho impazamo okanye izandla zigoso. Kodwa andikasiqondi isizathu sokuba idatha esekunene iqhawuke - ukusetyenziswa bekuqhelekile, okubonakalayo nakwigrafu yamazinyo.

Ngokubanzi, andizange ndikwazi ukufezekisa ukufaneleka kwale ndlela - igrafu phantse isoloko ibonisa uhlobo oluthile loqhekeko.

Ikhowudi efanayo yenzwa yemini.

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

Nceda uqaphele ukuba i-group_by parameter imiselwe kwisithuba, kwaye amanqaku_per_yure iparamitha ilawula yonke into. Kwaye apho kukho enye ingxaki ngeli candelo - points_per_hour isebenza kakuhle kwiitshathi zeyure okanye ngaphantsi, kodwa ifunxa kumathuba amakhulu. Ke ukuze ndifumane ikholamu enye ngosuku olunye, kuye kwafuneka ndifake ixabiso 1/24=0.04166666. Andithethi nangetshathi zeveki nezenyanga.

Indlela yoku-2

Ngelixa ndisaqonda umncedisi wasekhaya, ndifumene le vidiyo:

Umhlobo uqokelela idatha yokusetyenziswa kwiintlobo ezininzi zeesokethi ze-Xiaomi. Umsebenzi wakhe ulula kancinci - bonisa nje ixabiso lokusetyenziswa namhlanje, izolo kunye nenyanga. Akukho zishedyuli ezifunekayo.

Masizishiye iingxoxo malunga nokudityaniswa ngesandla kwemilinganiselo yamandla ngokukhawuleza - sele ndibhale ngasentla malunga "nokuchaneka" kwale ndlela. Akucaci ukuba kutheni engazange asebenzise amaxabiso okusetyenziswa aqokelelweyo, asele eqokelelwe kwi-outlet efanayo. Ngokombono wam, ukudibanisa ngaphakathi kwe-hardware kuya kusebenza ngcono.

Kwividiyo siza kuthatha umbono wokubala ukusetyenziswa kwexesha. Umfana ubala kuphela amaxabiso namhlanje nayizolo, kodwa siya kuqhubela phambili kwaye sizame ukuzoba igrafu. Undoqo wendlela ecetywayo kwimeko yam yile ilandelayo.

Masenze ixabiso eliguquguqukayo_at_the_beginning_of_yure, apho siza kubhala khona ukufundwa kweemitha zangoku.
Ukusebenzisa i-timer, ekupheleni kweyure (okanye ekuqaleni kwelandelayo) sibala umahluko phakathi kokufundwa kwangoku kunye nokugcinwa ekuqaleni kweyure. Lo mahluko uya kuba kukusetyenziswa kweyure yangoku - siya kugcina ixabiso kwi-sensor, kwaye kwixesha elizayo siya kwakha igrafu esekelwe kweli xabiso.
Kananjalo kufuneka "usete ngokutsha" ixabiso_at_beginning_of_hour ngokubhala ixabiso langoku lekhawuntara apho.

Konke oku kunokwenziwa ngomncedisi wekhaya ngokwakhe.

Kuya kufuneka ubhale ikhowudi engaphezulu kancinci kunendlela yangaphambili. Okokuqala, makhe senze ezi "inguqu" ezifanayo. Ngaphandle kwebhokisi asinalo iqumrhu "eliguquguqukayo", kodwa sinokusebenzisa iinkonzo ze-mqtt broker. Siza kuthumela amaxabiso apho kunye ne-retain = iflegi yokwenyani - oku kuya kugcina ixabiso ngaphakathi kwi-broker, kwaye inokukhutshwa apho nangaliphi na ixesha, naxa umncedisi wekhaya eqalwa ngokutsha. Ndenze iikhawunta zeyure nezemihla ngemihla ngaxeshanye.

- 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 kwi-automation, eqhuba iyure nganye nobusuku bonke ngokulandelelanayo.

- 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

Zombini ii-automation zenza iintshukumo ezi-2:

• Bala ixabiso lesithuba njengomahluko phakathi kwamaxabiso okuqala kunye nesiphelo
• Hlaziya ixabiso lesiseko sethuba elilandelayo

Ulwakhiwo lweegrafu kulo mzekelo lusonjululwa yigrafu eqhelekileyo yembali:

      - 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

Ijongeka ngolu hlobo:

Ngokomgaqo, oku sele kuyimfuneko. Inzuzo yale ndlela kukuba idatha yenziwa kube kanye ngekhefu. Ezo. kuphela iirekhodi ezingama-24 ngosuku kwitshathi yeyure.

Ngelishwa, oku akukayisombululi ingxaki ngokubanzi yesiseko esikhulayo. Ukuba ndifuna igrafu yokusetyenziswa kwenyanga, kuya kufuneka ndigcine idatha ubuncinane unyaka. Kwaye ekubeni umncedisi wekhaya ubonelela kuphela ukugcinwa kwexesha elilodwa kwisiseko sedatha yonke, oku kuthetha ukuba YONKE idatha kwinkqubo iya kufuneka igcinwe unyaka wonke. Ngokomzekelo, ngonyaka ndisebenzisa i-200 cubic metres yamanzi, oku kuthetha ukuba oku kuthetha ukungena kwe-200000 kwisiseko sedatha. Kwaye ukuba uthathela ingqalelo ezinye izinzwa, ngoko ke inani liba libi.

Indlela yoku-3

Ngethamsanqa, abantu abalumkileyo sele beyisombulule le ngxaki ngokubhala i-database ye-InfluxDB. Le database ilungiselelwe ngokukodwa ukugcina idatha esekwe kwixesha kwaye ilungele ukugcina amaxabiso eenzwa ezahlukeneyo. Inkqubo ikwabonelela ngolwimi olufana nombuzo lweSQL olukuvumela ukuba ukhuphe amaxabiso kwisiseko sedatha kwaye uwadibanise ngeendlela ezahlukeneyo. Ekugqibeleni, idatha eyahlukileyo ingagcinwa ngamaxesha ahlukeneyo. Umzekelo, ukufundwa rhoqo okuguquguqukayo okufana nobushushu okanye ukufuma kunokugcinwa iiveki nje ezimbalwa, ngelixa usetyenziso lwamanzi lwemihla ngemihla lunokugcinwa unyaka wonke.

Ngaphandle kwe-InfluxDB, abantu abakrelekrele bayila iGrafana, inkqubo yokuzoba iigrafu esekwe kwidatha evela kwi-InfluxDB. I-Grafana inokuzoba iintlobo ezahlukeneyo zeegrafu, zizenzele ngokweenkcukacha, kwaye, okona kubaluleke kakhulu, ezi grafu zinokuthi "zifakwe" kwi-lovelace-UI yomncedisi wasekhaya.

Fumana impembelelo apha и apha. Amanqaku achaza ngokweenkcukacha inkqubo yokufaka kunye nokudibanisa i-InfluxDB kunye neGrafana kumncedisi wekhaya. Ndiza kugxila ekusombululeni ingxaki yam ethile.

Ke, okokuqala, masiqale ukongeza ixabiso lekhawuntari kwi-influxDB. Isiqwenga solungiselelo lomncedisi wekhaya (kulo mzekelo ndiya konwaba ngokubanda kuphela, kodwa namanzi ashushu):

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

Masivale ukugcina le datha ifanayo kwisiseko sedatha somncedisi wekhaya wangaphakathi ukuze singaphinde siyiqhume kwakhona:

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

Ngoku masiye kwi-console ye-InfluxDB kwaye silungiselele ugcino lwethu lwedatha. Ngokukodwa, kufuneka uqwalasele ukuba idatha ethile iya kugcinwa ixesha elingakanani. Oku kulawulwa yinto ebizwa. umgaqo-nkqubo wogcino - oku kuyafana nogcino-lwazi olungaphakathi kwesiseko sedatha esingundoqo, kunye nesiseko sedatha sangaphakathi ngasinye sinemimiselo yalo. Ngokungagqibekanga, yonke idatha igcinwa kumgaqo-nkqubo wogcino obizwa ngokuba yi-autogen; le datha iya kugcinwa kangangeveki. Ndingathanda ukuba idatha yeyure igcinwe inyanga, idatha yeveki igcinwe unyaka, kwaye idatha yenyanga ingaze icinywe. Masenze umgaqo-nkqubo wogcino olufanelekileyo

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

Ngoku, enyanisweni, iqhinga eliphambili kukudibanisa idatha usebenzisa umbuzo oqhubekayo. Le yindlela eyenza umbuzo ngokuzenzekelayo ngezithuba ezikhankanyiweyo, idibanisa idatha yalo mbuzo, kwaye yongeza umphumo kwixabiso elitsha. Makhe sijonge umzekelo (ndibhala kwikholamu ukuze ifundeke, kodwa eneneni bekufuneka ndifake lo myalelo kumgca omnye)

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 myalelo:

• Yenza umbuzo oqhubekayo onikwe igama cq_water_cold_hourly kwisiseko sedatha somncedisi wekhaya
• Isicelo siya kwenziwa qho ngeyure (ixesha(1h))
• Isicelo siya kukrazula yonke idatha kumlinganiselo' homeassistant.autogen.l (ilitha), kubandakanywa ufundo lwamanzi abandayo nashushu.
• Idatha ehlanganisiweyo iya kuhlelwa yi-entity_id, eya kusinika amaxabiso ahlukeneyo abandayo kunye namanzi ashushu.
• Ukusukela ukuba ikhawuntala yelitha ilulandelelwano olunyukayo ngeyure nganye, kuyakufuneka ukuba kuthathwe elona xabiso liphezulu, ngoko ke udibaniso luyakuqhutywa ngumsebenzi max(ixabiso)
• Ixabiso elitsha liya kubhalwa kwi-homeassistant.month.water_meter_hour, apho inyanga ligama lomgaqo-nkqubo wogcino kunye nexesha lokugcinwa kwenyanga enye. Ngaphezu koko, idatha kumanzi abandayo kunye nashushu iya kusasazwa kwiirekhodi ezahlukeneyo kunye ne-entity_id ehambelanayo kunye nexabiso kwintsimi yexabiso.

Ebusuku okanye xa kungekho mntu ekhaya, akukho manzi asetyenziswayo, kwaye ke akukho mangeno amatsha kwi-homeassistant.autogen.l. Ukunqanda amaxabiso alahlekileyo kwimibuzo eqhelekileyo, ungasebenzisa ukugcwalisa (ngaphambili). Oku kuya kunyanzela i-InfluxDB ukuba isebenzise ixabiso leyure yokugqibela.

Ngelishwa, umbuzo oqhubekayo unento ekhethekileyo: iqhinga lokuzalisa(elidlulileyo) alisebenzi kwaye iirekhodi azenziwanga. Ngaphezu koko, olu luhlobo oluthile lwengxaki engenakugqithiswa ukuba sele kuxutyushwe iminyaka eliqela ngoku. Siza kujongana nale ngxaki kamva, kodwa masigcwalise (ngaphambili) kumbuzo oqhubekayo - awuphazamisi.

Makhe sijonge ukuba kwenzeke ntoni (ewe, kufuneka ulinde iiyure ezimbalwa):

> 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 ukuba amaxabiso akwisiseko sedatha agcinwe kwi-UTC, ngoko olu luhlu luhluke ngeeyure ezi-3 - ixabiso le-7am kwimveliso ye-InfluxDB ihambelana ne-10am kwiigrafu ezingentla. Kwakhona qaphela ukuba phakathi kwe-2 kunye ne-5 am akukho zirekhodi - olu luphawu olufanayo lombuzo oqhubekayo.

Njengoko ubona, ixabiso elidityanisiweyo likwakulandelelwano olunyukayo lwe-monotonically, amangeno kuphela ayenzeka kancinci-kanye ngeyure. Kodwa oku akuyongxaki - sinokubhala omnye umbuzo oza kubuyisela idatha echanekileyo 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)

Ndiza kucacisa:

• Kwisiseko sedatha ye-homeassistant.month.water_meter_hour siya kukhupha idatha ye-entity_id='water_meter_cold' yosuku lokugqibela (ixesha >= ngoku() -24h).
• Njengoko besenditshilo, amanye amangeno asenokuba alahlekile kulandelelwano lwe-homeassistant.month.water_meter_hour. Siza kuhlaziya le datha ngokuqhuba umbuzo nge GROUP BY time(1h). Eli xesha gcwalisa(elidlulileyo) liza kusebenza njengoko kulindelekile, livelisa idatha engekhoyo (umsebenzi uya kuthatha ixabiso langaphambili)
• Eyona nto ibalulekileyo kwesi sicelo ngumsebenzi wokumahluko, oya kubala umahluko phakathi kwamanqaku eyure. Ayisebenzi yodwa kwaye ifuna umsebenzi wokudibanisa. Le mayibe yimax() esetyenziswe ngaphambili.

Isiphumo sokwenziwa sijongeka ngolu hlobo

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

Ukususela kwi-2 ukuya kwi-5 am (UTC) kwakungekho ukusetyenziswa. Nangona kunjalo, umbuzo uzakubuyisela ixabiso elifanayo lokusetyenziswa enkosi ukugcwalisa (ngaphambili), kwaye umahluko umsebenzi uzakususa eli xabiso ngokwalo kwaye imveliso iya kuba ngu-0, eyiyo kanye into efunekayo.

Ekuphela kwento eseleyo kukwakha igrafu. Ukwenza oku, vula iGrafana, vula esele ikhona (okanye yenza entsha) ideshibhodi, kwaye wenze indawo yolawulo entsha. Izicwangciso zetshathi ziya kuba ngolu hlobo.

Ndiza kubonisa idatha yamanzi abandayo kunye ashushu kwigrafu efanayo. Isicelo siyafana ncam nale ndisichaze ngasentla.

Iiparamitha zokubonisa zisetwe ngolu hlobo lulandelayo. Kum kuya kuba yigrafu enemigca, ehamba ngamanyathelo (izitebhisi). Ndiza kucacisa iparamitha yeSitaki esingezantsi. Kukho ezinye iinketho zokubonisa ezingezantsi, kodwa azinamdla kangako.

Ukongeza itshathi enesiphumo kumncedisi wasekhaya kufuneka:

• phuma kwindlela yokuhlela itshathi. Ngesizathu esithile, izicwangciso zokwabelana ngetshathi ezichanekileyo zinikezelwa kuphela kwiphepha ledeshibhodi
• Cofa kunxantathu ecaleni kwegama letshathi kwaye ukhethe ukwabelana kwimenyu
• Kwifestile evulayo, yiya kwindawo yokulungisa isithuba
• Sukuqwalasela uluhlu lwexesha langoku - siza kucwangcisa uluhlu lwexesha nge-URL
• Khetha isihloko esifunekayo. Kwimeko yam kukhanya
• Khuphela i-URL enesiphumo kwikhadi lezicwangciso 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"

Nceda uqaphele ukuba uluhlu lwexesha (iintsuku ezi-2 zokugqibela) zisetiwe apha, kwaye hayi kwizicwangciso zedeshbhodi.

Igrafu ibonakala ngolu hlobo. Andizange ndisebenzise amanzi atshisayo kwiintsuku ze-2 zokugqibela, ngoko ke igrafu yamanzi abandayo kuphela izotywa.

Andikazigqibeli ngokwam ukuba yeyiphi igrafu endiyithanda ngcono, inyathelo lomgca, okanye imivalo yokwenyani. Ke ngoko, ndiza kunika nje umzekelo wetshathi yokusetyenziswa kwemihla ngemihla, ngeli xesha kuphela kwimivalo. Imibuzo yakhiwe ngokufanayo naleyo ichazwe ngasentla. Iinketho zokubonisa zezi:

Le grafu ibonakala ngolu hlobo:

Ke malunga neparamitha yeStack. Kule grafu, ikholamu yamanzi abandayo izotywa phezu koluhlu lwamanzi ashushu. Ubude obupheleleyo buhambelana nokusetyenziswa okupheleleyo kwamanzi abandayo kunye ashushu ngexesha.

Zonke iigrafu ezibonisiweyo ziyatshintshatshintsha. Ungahambisa imouse yakho kwindawo yomdla kwaye ubone iinkcukacha kunye nexabiso kwindawo ethile.

Ngelishwa, kwakukho iimpukane ezimbalwa kwi-ointment. Kwitshathi yebha (ngokungafaniyo netshathi enemigca yesinyathelo), umbindi webar awukho phakathi kwemini, kodwa ngo-00:00. Ezo. isiqingatha sasekhohlo sekholamu izotywe endaweni yosuku lwangaphambili. Ke iigrafu zangoMgqibelo nangeCawa zizotywe kancinane ngasekhohlo kwendawo eluhlaza okwesibhakabhaka. Ndide ndacinga ukuba ndingayoyisa njani.

Enye ingxaki kukungakwazi ukusebenza kakuhle ngamaxesha athile enyanga. Inyani kukuba ubude beyure / imini / iveki imisiwe, kodwa ubude benyanga buhluke ngexesha ngalinye. I-InfluxDB inokusebenza kuphela ngamaxesha alinganayo. Ukuza kuthi ga ngoku iingqondo zam zanele ukuseta ikhefu elimiselweyo leentsuku ezingama-30. Ewe, igrafu iya kudada kancinci unyaka wonke kwaye imivalo ayiyi kuhambelana ncam neenyanga. Kodwa kuba ndinomdla kule nto nje njengemitha yokubonisa, ndilungile nayo.

Ndibona ubuncinci izisombululo ezibini:

• Nikeza iitshathi zenyanga kwaye uzilinganisele kwiiveki ngeeveki. Iibhari zeveki ezingama-52 zonyaka zikhangeleka zilungile
• Cinga ukusetyenziswa kwenyanga ngokwayo njengendlela ye-2, kwaye usebenzise i-grafana kuphela kwiigrafu ezintle. Iya kuba sisisombululo esichanekileyo. Ungade ugqume iigrafu zonyaka ophelileyo ukuze uthelekise - igrafana nayo ingayenza loo nto.

isiphelo

Andazi ukuba kutheni, kodwa ndithe phithi zezi ntlobo zegrafu. Babonisa ukuba ubomi buyaguquguquka kwaye yonke into iyatshintsha. Izolo bekuninzi, namhlanje kuncinci, ngomso kuya kuba yenye into. Ekuphela kwento eseleyo kukusebenza namalungu osapho ngomxholo wokusetyenziswa. Kodwa kunye neminqweno yangoku, nje inani elikhulu kwaye elingaqondakaliyo kwisiliphu sokuhlawula sele lijika libe ngumfanekiso oqondakalayo wokusetyenziswa.

Ngaphandle komsebenzi wam weminyaka engama-20 njengomdwebi wenkqubo, andizange ndidibane noovimba beenkcukacha. Ngoko ke, ukufaka i-database yangaphandle kwakubonakala ngathi yinto engabonakaliyo kwaye engaqondakaliyo. Itshintshe yonke into inqaku elingasentla - kwavela ukuba ukudibanisa isixhobo esifanelekileyo kwenziwa kwiinqakrazo ezimbalwa, kwaye ngesixhobo esikhethekileyo, umsebenzi wokucwangcisa iitshathi uba lula kancinci.

Kwisihloko ndikhankanye ukusetyenziswa kombane. Ngelishwa, okwangoku andinako ukubonelela naziphi na iigrafu. Enye imitha ye-SDM120 yafa ngenxa yam, kwaye enye i-glitchy xa ifunyenwe nge-Modbus. Nangona kunjalo, oku akuchaphazeli isihloko seli nqaku nangayiphi na indlela - iigrafu ziya kwakhiwa ngendlela efanayo namanzi.

Kweli nqaku ndibonise iindlela endizizame ngokwam. Ngokuqinisekileyo kukho ezinye iindlela zokucwangcisa ukuqokelela idatha kunye nokubonwayo endingaziyo. Ndixelele malunga namazwana, ndiya kuba nomdla kakhulu. Ndiya kukuvuyela ukugxeka okwakhayo kunye neengcamango ezintsha. Ndiyathemba ukuba izinto ezizisiweyo ziya kunceda umntu.

umthombo: www.habr.com