How and where does Domoticz store raw measurements? Topic is solved

[CharlesL]

Does anybody know where and how Domoticz stores raw (temperature) mesurements?
In particular: does it keep everything?

I want to export them for off-line analysis.

[CharlesL]

It looks (but I can't be completely sure) as if Domoticz stores everything (configuration, scenes, measurements) in one single database file:

/home/pi/domoticz/domoticz.db

Does anyone know what format this file is? Are there any standalone applications that can read it?

More in general: is there any documentation on Domoticz at a level between the end-user manual and the source code?

[SweetPants]

It looks (but I can't be completely sure) as if Domoticz stores everything (configuration, scenes, measurements) in one single database file: /home/pi/domoticz/domoticz.db

That is correct

Does anyone know what format this file is?

It's a SQLite database

Are there any standalone applications that can read it?

I use SQLIteman on Linux, there are also Windows tools, google is your friend

More in general: is there any documentation on Domoticz at a level between the end-user manual and the source code?

no, search the forum for more information

[georgesattali]

It's a sqlite3 database.
be careful with it, you may try some commands if u know sql :
In fact, after some thoughts, I would suggest not to use it.

Code: Select all

pi@raspberrypi ~ $ sqlite3 /home/pi/domoticz/domoticz.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .tables
BackupLog             Meter                 Scenes
Cameras               Meter_Calendar        SetpointTimers
CamerasActiveDevices  MobileDevices         SharedDevices
CustomImages          MultiMeter            Temperature
DeviceStatus          MultiMeter_Calendar   Temperature_Calendar
DeviceToPlansMap      MySensors             Timers
EnoceanSensors        MySensorsChilds       ToonDevices
EventActions          MySensorsVars         UV
EventMaster           Notifications         UV_Calendar
EventRules            Percentage            UserSessions
Fan                   Percentage_Calendar   UserVariables
Fan_Calendar          Plans                 Users
FibaroLink            Preferences           WOLNodes
Floorplans            Rain                  Wind
Hardware              Rain_Calendar         Wind_Calendar
HttpLink              SceneDevices          ZWaveNodes
LightSubDevices       SceneLog
LightingLog           SceneTimers

sqlite> .schema Temperature
CREATE TABLE [Temperature] ([DeviceRowID] BIGINT(10) NOT NULL, [Temperature] FLOAT NOT NULL, [Chill] FLOAT DEFAULT 0, [Humidity] INTEGER DEFAULT 0, [Barometer] INTEGER DEFAULT 0, [DewPoint] FLOAT DEFAULT 0, [SetPoint] FLOAT DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')));
CREATE INDEX t_idx on Temperature(DeviceRowID);

sqlite> .quit

[georgesattali]

May I suggest a script of mine that stores several values in an excel compatible file.
It might be considered ugly. It's simple but data files may grow rather big :
2 files are created : daily.csv and monthly.csv
All data from the list "devices" are dumped into daily.csv every 5 minutes.
Every day, at 20:35, daily.csv is concatenated to monthly.csv.

You may use it with excel or with https://plot.ly which is fine and understands that kind of files.

Both directories /var/tmp/ and /mnt/pc only exists on my own raspb., you can use for ex.

fichierJournalier="/tmp/daily.csv"
fichierMensuel="/tmp/monthly.csv"

Code: Select all

-- ********************************************************************************
--
-- Called every minute, records current values of device list every 5 minutes
--    1) yeah, current, no avg - want it simple, doesn't need more
--    2) won't see less than 5 min events - sees switches if they last longer than 5min
--
-- comma "," is the separator => european users may double click csv files to Excel
-- Date and Time in 2 different columns, Excel treats days oddly but hours ok,
-- "plot.ly" (see below) recognizes them and concatenate them nicely (thanks, plotly).
--
-- You can mix all kind of devices in device list (temps, switch, meteo, temphum,
-- thermostat, text; etc...)
--
-- easily used with https://plot.ly/     (great, bit slow to load, free for personnal use, ++)
--
-- switch On Off are replaced with 15-0 to be clearly seen with temps around 20°C
-- 
-- ********************************************************************************
-- Variables 

fichierJournalier="/var/tmp/daily.csv"            -- actually, mine is on a tmpfs dir (RAM Drive)
fichierMensuel="/mnt/pc/monthly.csv"              -- on cifs dir, shared with Windows

devices = {"Température Chambre"
	   , "Température Salle de Bain"
	   , "Thermostat Chambre"
	   , "Radiateur Chambre 1"
	   , "Temp dehors"
	   , "Confort Chambre"
	   , "Météo Baromètre"
	   , "Thermostat Salon"
	   -- , "JourRouge"
	   , "Absence"
	   -- , "JourFerie"
	   -- , "JourDeSemaine"
	   -- , "CouleurDuJour"
	   -- , "CouleurDemain"
	   , "Prise 2 (Box)"
	   , "Prise 3 (Ordinateur)"
	   , "Prise 4"
	   , "TempHum1"
	   , "TempHum2"
	   -- , "Arrosage1"
	   -- , "Arrosage2"
}

-- ********************************************************************************
commandArray = {}
print('Script v01 strip_time_CSV.lua')

function debug(m)
   print("......CSV "..m)
end


now=os.date("*t")
-- ça commence ici
-- ********************************************************************************

-- debug ("TempHum1="..otherdevices_svalues['TempHum1'])
-- debug ("Météo Baromètre="..otherdevices_svalues["Météo Baromètre"])

if now.min % 5 == 0 then
   -- debug("début")
   -- create file if needed : write headers to file
   f=io.open(fichierJournalier, "r")
   if f == nil then
      -- debug("création du fichier journalier")
      f=io.open("/tmp/a.txt", "w")
      f:write("Date,Time")                         -- separator = comma
      for i,d in ipairs(devices) do

	 -- special treatment for headers of multi valued datas :
	 -- check data type 

	 v=otherdevices[d]                        -- v is the value attached to device d
	 debug("d="..d.."="..(v or "nil"))
	 if v==nil or v=="" then                  -- multi valued ?
	    v=otherdevices_svalues[d] or ""
	    --debug("d="..d.."="..v)
	    v,nbCommas=string.gsub(v,";",",")     
	    if nbCommas==0 then
	       f:write(",", d) 
	    else                                  -- write it as Meteo 1, Meteo 2, ...
	       for i=1,nbCommas+1 do f:write(",", d.." "..i) end
	    end
	 else
	    f:write(",", d)                       -- separator = comma
	 end
	 
      end
      f:write("\n")
      f:close()
      -- made to go on a cifs directory, shared with Windows
      os.execute("iconv -f utf8 -t ISO-8859-1 /tmp/a.txt > '"..fichierJournalier.."'")
      os.execute("chmod 666 '"..fichierJournalier.."'")
   else
      f:close()      
   end                                            -- ok, header is now created

   
   -- do the stuff (generate datas)
   -- debug("génération des données")
   f=io.open(fichierJournalier, "a")
   f:write(os.date("%Y-%m-%d,%H:%M"))             -- separator = comma
   for i,d in ipairs(devices) do
      v=otherdevices[d]
      -- debug("d="..d.."="..(v or "nil"))
      if v==nil  or v=="" then                    -- multi valued ?
	 v=otherdevices_svalues[d] or ""
	 v=string.gsub(v, ";", ",")               -- separator = comma
      end
      v=string.gsub(v,"On","15")                  -- yeah, On=15, Off=0
      v=string.gsub(v,"Off","0")
      f:write(",", v)                             -- separator = comma
   end
   f:write("\n")
   f:close()

   -- once a day, concat daily file to monthly file,
   -- 1) could be any TIME COMPATIBLE WITH % 5 ABOVE
   -- 2) if fichierMensuel is on a cifs dir, you have to choose
   --    a time where your PC is on.
   if now.hour == 20 and now.min == 35 then
      debug("concaténation dans le fichier mensuel")
      f=io.open(fichierMensuel, "r")
      if f == nil then
	 os.execute("head -n 1 '"..fichierJournalier.."' >> '"..fichierMensuel.."'")
      else
	 f:close()
      end
      os.execute("tail -n +2 '"..fichierJournalier.."' >> '"..fichierMensuel.."'")
      fMensuelOk=io.open(fichierMensuel, "r")
      if fMensuelOk == nil then
	 debug("Pas de suppression car impossible de lire "..fichierMensuel)
      else
	 os.execute("/bin/rm -f '"..fichierJournalier.."'")
	 debug("Suppression de "..fichierJournalier)
	 fMensuelOk:close()
      end
   end
end


-- ********************************************************************************
-- la derniere ligne est obligatoire !
return commandArray

[CharlesL]

@Sweetpants
Thanks, your answer is complete and it was the first. Too bad about the docs, but such is Open Source life

@georgesattali
Thanks for the hint. I trust that if I open domotiz.db for reading and don't start locking any tables or rows I won't interfere with the workings of Domoticz itself? I know a bit about databases and I'll have an good look at domoticz.db.

Your script is interesting (what language is it in? I don't recognise it), and may be just what I was looking for. I want logging, not averaging. And with a 32 Gb. micro SD card (for 10 Euros or so) I don't care about file sizes (for the next three to four years at least).

I see you're not touching the database domoticz.db in your script. Do I understand correctly that the line:

v=otherdevices[d]

is where you get the current readings for all devices? Those variables are somehow maintained by the Domoticz system? That's the kind of stuff that ought to be documented somewhere. Thanks for sharing.

[georgesattali]

Hello Charles,

it is an Lua script, that goes into the /home/pi/domoticz/scripts/lua,
with a name like "script_time_xxx.lua" it will be called automatically every minutes by Domoticz (but really does something only every 5 mins).
Alternatively, one can copy it to the new script editor integrated in "Setup/More options/Events/", choose "Lua" instead of Blockly, choose Time instead of All.
About doc, do you know this page : https://www.domoticz.com/wiki/Domoticz_Wiki_Manual

Reading values is done throuh an lua loop meaning : for each name "d" of the list "devices", get value of "d" (otherdevices[d]) ...

Code: Select all

   for i,d in ipairs(devices) do
      v=otherdevices[d]
      ... use v
   end -- of loop

Have a nice day, GD

[Marci]

Use MQTT via node-red subscribed to domoticz/out, where you can then use write-to-file nodes to capture every message, or push the readings into influxdb... (all should be included/configured on standard SD-image - head to http://domoticzip:1880 to get to the node-red interface)

See examples in wiki for MQTT / node-red to get you started.