I save my data from my motion detector (Philips Hue) persistently in a PostgreSQL database. Unfortunately the tables look like this:
Timestamp without Timezone | status
2021-07-21 14:35:21 | ON
Is it possible to automatically format the storage of the data in a different table format? So that openHAB automatically saves / transfers them in this format?
For example:
Weekday | Date | Time | status
1 | 2021-07-21 | 14:35:21 | ON
Is there a simple and quickly understandable way of doing this? The weekday should then stand 1-7 for Monday - Sunday (think that is also common?)
Thank you in advance and best regards (sorry for bad english )
Tobi
I would like to write a small algorithm. This should automatically switch the light on or off at a certain time, depending on what the average of the times of the previous days was (in each case the average of the corresponding related weekdays - for Monday all past Mondays, for Saturday all past Saturdays, …). I currently still have problems how to process this data, and whether there are possibly ways to do this via Rules or something similar. (It is not about fixed times for each day).
I hope you understand what I mean - bad English. Sorry
However, given amount of work it involves and impact you can cause on your installation I would rather advice you to create a materialized view. With such a view you can keep both words happy. PostgreSQL is quite advanced database engine and I believe it is able to extract time and weekday within single SQL query by using dedicated functions. This means that you don’t need to change table structure, just write appropriate SQL query.