How to get first change (or update) this(or last) month from persistence?

Hello all,

I am working on graphing the daily states of a persisted String Activity "Current Activity" item.

The states (text strings) are already being graphed by grafana’s discrete plugin and iv’e currently embedded the graph with a “now-10h” time span into my sitemap (so, from the morning, when activity starts the grpah gets filled in gradually with states, and in the evening one gets a decent representation of the daily activities).
(Were I to find a solution to my question below, that “now-10h” could be changed as well.)

The state, at the end of day is returned to “null”(as string, it’s just arbitrary, could have been a “-” or whatever) to which i have mapped colour black in the graph.

This is quite ok, but my actual objective is a monthly export of these graphs divided day by day, from the beginning of activities in the morning, until end of activities in the evening.

What i’m attempting to work towards would be somehting along the lines of a cron scheduled rule (that part i need no assistence with) that :

on the last day of the month late in the eveneing, 

find the first day of the current month
find the first change from "null" on that day and return the timestamp 
find the last change to "null" on that day and return the timestamp
loop to the next day
rinse and repeat for every day of the month until now
feed the timestamps to grafana to export rendered images of the grafs// generate a txt file with all the links to the graphs rendered as images with correct timestams.

I need no assistence with datetime formats conversions.
I’m no expert but will enjoy sorting out the shell script exporting bit on my own.

What i’m rather trying to understand is what logic could/should i follow to achieve it .
The loop could also start from today and go backwards i.e. but i don’t know of a persistnece extension.timestamp that could help me.

Any hint is welcome, if i manage to make this work, once cleaned, will post the complete solution including the work i’ve already done (there is quite a bit to it), i’m sure many could make use of a monthly time sheet report of activites , freely extendable to more objects/subjects(that i’ve also already sorted out with member of group triggers and org.eclipse.smarthome.model.script.ScriptServiceUtil).

i’m stil working with rules DSL

Why do you need to be so precise? Just go from midnight to midnight for your timestamps. You will still get all the values for the day.

And it is not clear why you need to loop through all the days. You want 30 charts? What do you want to do with these charts?

This seems way over complicated and I’m not sure I see what you are trying to do or why you just don’y browse the data in Grafana to get the information you are after.

The following posts might provide some help.

Assuming you are after a separate image per day, it seems to me you just need to get the first day of the month, let’s start at midnight, (now.withTimeAtStartOfDay.withDayOfMonth(1)) and the current day of the month (now.dayOfMonth) and then loop through that number of days to generate the time information you need to pass to Grafana (you’ll need to look at the grafana docs).

var day = now.dayOfMonth
while(day > 0){
    // generate timestamps needed by grafana based on now.withTimeAtStartOfDay.minusDays(day)
    // generate chart
    day = day - 1

Persistence is not needed here. Keep it simple.

Ofc is not clear, i omitted it because i was trying to get straight to the point rather than make people read an EVEN LONGER post and be deterred to reply by boredom. i find that doing so is not good etiquette and generally a waste of the reader’s time. Since you asked, i will reply going against my idea of etiquette…

Just re-iterating: i am not trying to dupmp an issue on others’ shoulders, i am trying to pick clever and more expert people’s brains on a specific issue which represents maybe 10% of the final wanted result of a wider project(below) wihtin OH, which i am putting together without constantly weighing on you all, and will share the “how to” once completed and cleaned to the best of my abilities.

Here it goes:

At work we compile BY HAND an absolutely horredous and unwieldy excel file(i deeply hate it) with the time we spend doing our various tasks daily. This is not by choice, this is imposed by the higher ups, and i would generally consider it good practice if done properly. (There also are softwares which are built for time management.)

Ideally, if one(me) really takes the time to insert task id and the time spent while the day passes, would get precise graphing and statistics and be able to derive monhly work hours(costs) per project.

In the real world, this means interrupting the stream of thoughs of what you are doing, loosing concentration to make caluclations about how many minutes you’ve spent on the previous task, before passing onto the next task at hand.

We also work in an open space environment, and often help each other.
Again, ideally, each time somebody asks for help, and you know how to help them, you’d need to stop what you are doing, tell them “i’ll help you” count the minutes you spent on your previous task, mark your stuff as task id, go help them, and then come back, count your minutes and input them, reinsert the previous task… and by then you have completely forgotten what was the next thing you wanted to do. You can obviously leave post-it notes, make guides for yourself (again good practice) but… really that’s only a support, not a solution.

SO, i talked with management, and proposed to implement a system that would achieve the same result (daily graphs and monthly totals) ,with more more coherence to effectively what is going on in the office, while not encumbering us workers with an (oh so italian) overhead of bureaucracy.

Note that i’ve already implemented through openhab the management of ventilation and lighting in the office, so it felt only natural, since we already have a machine running for that, to exploit it further (and yes, i like challenges)

it’s worse than that. I want to filter out the days when there is no change in state, as none of us works 7/7. But that part i can do on my own and will not ask help for it.
i want to provide links to each of the rendered images or copypaste them in a monthly report.

Because by filling in manually my hated excel is what we currently get, and in the adminstration’s eyes if they are to accept another system it must provide the same level of analysys.

The purpose of automation is often “saving time”. I’ve read you scripted your automated installation of OH, i believe it’s for the same purpose - streamlining, i really dont’ think it needs explanation.

The main issues are readability and resolution


(this was achieved as a test, so i haven’t inputted many minutely changes, but imagine a more fragmented day, the loss of information cha be significant)

Because we also all dont’ work full days on equal working hours. some of us are part time, some of us work more often mornings, others afternoons into evening, some always 8.30am-5pm.
Again, by filling in that excel file, the result adapts automatically(…filed by hand…) to each of our hours.

We already produce bar codes for each task (alltho for different purposes linked to iso9001 certification.
You might already know that bar code readers work often asn HID.
Add it to:

And this is the part i have already sorted out, together with calcualtions for totals, bash scripting and

this is already done too. i know the format of the strings i need.

i am missing only one piece, which is finding out timestamps.

i am truly sorry for the lenghty post. :frowning:

PS. I’ll keep looking for solutions on my own as well.
If i find out how to solve my issue will post here

Against that, asking “how do I perform function X” limits the answers, while “how do I achieve objective Y” may offer solutions you haven’t thought of. Don’t be shy about explaining objectives :smiley:

I don’t think it’s going to yield a magic solution here though - I rather expect your approach of doing it as donkey work, searching each days results the hard way, will be it.

Don’t forget to account for someone working over midnight - maybe you need to find the first and last event of any day, and then work out if is “null”. If it isn’t, you would need to look back or pass forward the condition.

Luckily that might happen only once a year… but yes, you are right!
i think i could solve it by a brief reset to "null " at 23:59:57 and restore at 00:00:03 i.e.

TY for the input!

Well, I was thinking it might simplify your retrieval issue - you don’t care about the first/last null of the day, just the first/last event of any type.
Then if it isn’t null, you might do fakery by looking at yesterdays/tomorrows last/first (of any type) to identify the type to fake at 00:00.

val [closure of working day 4 days ago]=Person_A.historicState(now.minusDays(3).withTimeAtStartOfDay).timestamp.time

I believe I’ll need to loop backwards to finid the beginning of the working day.


solved it!
it’s complicated though.
i’m placing aswitch item on each person’s sitemap.
once you click ON, your working day starts, your relelvant portion of sitemap is made visible.
once you click off, your status returns to null, your “at work” bit of sitemap disappears

The good bit is:

if i go backwards from the historicstate’s timestamp i posted above only of an hour and ask for the historicstate timestamp of the switch, i get the beginning of the working day.

now i only have to write it! :grimacing:
(PS. in fact, it’s not too complicated, just changes the logic :wink: )

This runs a high risk of becoming the XY Problem. You ask how to do a very specific technical thing when the real answer is that there is a better over all approach. When you don’t give us the context you prevent us from giving you a better approach that may eliminate the need to even do your specific technical thing.

Based on your description of what you want to do, openHAB is just about as horrible of a tool to achieve your ends as Excel is. openHAB doesn’t provide generic query abilities of a database. You can only ask for what state an item was in at a certain date time. You can’t ask “when did the item change state after a given date time” for example. You can do some other queries to, none of which are relevant to this problem.

You need to be able to access the database directly to find the timestamps and you cannot do that from Rules or through OH in any reasonable way. It would be far far easier and work better to do it from a shell script that can query the database with the full capabilities the database supports.

If you insist on doing this in Rules, you might want to use executeCommandLine to query the database using the database’s native client instead of the Persistence extensions. You can get the timestamp with one query.

Yup, already prepared a shell script with the curl calls to influxdb and email output with the monthly report in CSV format, integrated into a rule with executeCommandline action.
That part of already in working order, Ty :slight_smile:
Now I have re-read your reply!
I’ll see if there is a way to get the first update of a series in a given day straight from influxdb, Ty!