search

Mohlsson / ReplayLightsHistory

AppDaemon App for Home Assistant to replay light switch history when no one is home.
Apache License 2.0
29 stars 5 forks source link

error: Unknown column 'created' in 'field list #30

Closed xkill closed 9 months ago

xkill commented 9 months ago

Error with MariaDB: Using latest stable version of Home Assistant 

File "/usr/lib/python3.11/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/usr/lib/python3.11/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3.11/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, "Unknown column 'created' in 'field list'")
xkill commented 9 months ago

The MariaDB query should be similar to the SQLite query (seems that MariaDB query was not updated when the sqlite query was updated): This should work:

        query = f'SELECT states_meta.entity_id, states.state, states.last_updated_ts FROM states \
                        JOIN states_meta ON states_meta.metadata_id = states.metadata_id \
                        WHERE states_meta.entity_id LIKE "{self.devType}%" \
                        AND states.last_updated_ts > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL {days_back} DAY)) \
                        AND states.last_updated_ts <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL {days_back} DAY) + INTERVAL 1 HOUR)'
Mohlsson commented 9 months ago

Thank you for fixing this.