You have a GPS presence detection in your openHAB instance configured? So why not using your data and create your privately stored CSV time sheets for your presence somewhere? Or you create time sheets how long a specific switch is active every month.

I wrote the python script dbtimesheet.py which can be used to create CSV timesheets based on your database data. The script uses SQL commands and should work with all SQL databases but is currently only tested with my MariaDB database. You can find this script in GitHub here: openHABdbTools.

You can copy this script to /etc/openhab2/scripts and give him execution rights with „chmod +x *.py“.

This description assumes that you have an GPS tracking running on your openHAB and smartphone like described here.

As an example you can track your time at work in a spreadsheet. If you have configured a switch item which is switched on and off, like described here, you can use the data to create a time sheet which you read with Excel.

First you need to configure the time sheet in dbtools.ini. You can configure up to 9 time sheets which are created every month. The time shhets should be called [timeSheet1]…[timeSheet9]:

[root]
#root path for all created files
path=/etc/openhab2/data/
#uncomment this when you have a german Excel Version. default is ','
delimiter=;

[dbconfig]
#host address of the db server:
host=localhost      	
#openHAB table:
table=openhab   
#openHAB data base user:
user=openhab	
#openHAB data base password
password=openhab		

# You can add more timeSheets with sections [timeSheet1] - [timeSheet9]
[timeSheet1]
#path where the time sheet ist strored
path=TimeSheet/
#item to create time sheet (must be of type switch)
item=OliverAtWork
#name of the timesheet. Used in CSV filenames.
name=AtWork
#column names for switch states (default: "on"/"off")
on=Enter
off=Leave
#column name with Total value (default: "total")
total=Working hours
#column name with date value (default: "date")
date=Date
#number of events in table (default=2)
events=2

In this example we create one time sheet when the work place location is entered or left with the name=AtWork.

Note:
Excel versions for different countries load CSV files different. if you use a English Excel version the default delimiter ‚,‘ works fine. For German Excel versions you use better ‚;‘. this can be set with the delimiter= value.

The data base data of the switch item=OliverAtWork is used for sheet creation. The time sheets are stored in a directory structure based on years in /etc/openhab2/data/timesheet/:

You can influence the naming of the columns with on=, off=, total= and date=. With events= you configure the number ON->OFF events which are visible in the sheet.

In this example you get a time sheet like this:

Would be nice to get feedback if the script is also working for your installation and which persistence configuration you use.

Another example could be that you want to see how long you WC light is on each month. You can create a time sheet like this:

[timeSheet2]
#path where the time sheet ist strored
path=/etc/openhab2/data/TimeSheet/
#item to create time sheet (must be of type switch)
item=S_WC_Light
#name of the timesheet. Used in CSV filenames.
name=WCLight
#number of events in table (default=2)
events=5

Now you have an additional time sheet like this:

Don’t be confused that the total time of 0.48 hours does not match 100% with the sum of the total values of the days. The total time of the month also consider the seconds which are not seen in the day sums.

Would be nice to get feedback if the script is also working for your installation and which persistence configuration you use.