Automatic formatting JDBC PostgreSQL in different table layout

Hello,

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 :wink: )
Tobi

No, there is no way to customize how openHAB stores the data in the database.

why do you want to do that ? Isn’t that redundant information as you could create all the information based on the timestamp.

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

Kind regards

You can do the

  • processing of the data outside of OH e.g. controlled by a crontab job
  • read the data from JDBC database by a shell script using JDBC client
  • calculate the new times for the new day at midnight
  • feed the new times into an at job
  • the at job switches the light on/off at a specific time via REST API call ( curl )

There is a way. Most of JDBC queries can be customized. This means that you could get own table structure and customized queries for inserts. Have a look on https://github.com/openhab/openhab-addons/blob/main/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/db/JdbcBaseDAO.java

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.

Best,
Łukasz