search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
73.46k stars 30.69k forks source link

SQL Integration - Error if setting device class to timestamp #127661

Open Juchar opened 1 month ago

Juchar commented 1 month ago

The problem

Setting the device class to timestamp will result in an entity being unavailable due to an error found in the log, even if formatting the sql output correctly using the value template, e.g.: {{ strptime(value.strip(), "%Y-%m-%d %H:%M:%S").astimezone().isoformat() }}

Most likely also reproducable simply using this value template with any query and simply setting the device class to timestamp: {{ strptime(now(), "%Y-%m-%d %H:%M:%S").astimezone().isoformat() }}

What version of Home Assistant Core has the issue?

core-2024.9.3

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

SQL

Link to integration documentation on our website

https://www.home-assistant.io/integrations/sql/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Logger: homeassistant.components.sensor
Quelle: helpers/entity_platform.py:595
Integration: Sensor ([Dokumentation](https://www.home-assistant.io/integrations/sensor), [Probleme](https://github.com/home-assistant/core/issues?q=is%3Aissue+is%3Aopen+label%3A%22integration%3A+sensor%22))
Erstmals aufgetreten: 18:58:31 (5 Vorkommnisse)
Zuletzt protokolliert: 19:00:45

Error adding entity sensor.ts_test_13 for domain sensor with platform sql
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 594, in state
    if value.tzinfo is None:
       ^^^^^^^^^^^^
AttributeError: 'str' object has no attribute 'tzinfo'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 595, in _async_add_entities
    await coro
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 909, in _async_add_entity
    await entity.add_to_platform_finish()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1365, in add_to_platform_finish
    await self.async_added_to_hass()
  File "/usr/src/homeassistant/homeassistant/components/sql/sensor.py", line 340, in async_added_to_hass
    await self.async_update()
  File "/usr/src/homeassistant/homeassistant/components/sql/sensor.py", line 353, in async_update
    self._process_manual_data(data)
  File "/usr/src/homeassistant/homeassistant/helpers/trigger_template_entity.py", line 234, in _process_manual_data
    self.async_write_ha_state()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1005, in async_write_ha_state
    self._async_write_ha_state()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1130, in _async_write_ha_state
    self.__async_calculate_state()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1067, in __async_calculate_state
    state = self._stringify_state(available)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1011, in _stringify_state
    if (state := self.state) is None:
                 ^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 605, in state
    raise ValueError(
ValueError: Invalid datetime: sensor.ts_test_13 has timestamp device class but provides state 2024-06-28T20:00:10+02:00:<class 'str'> resulting in ''str' object has no attribute 'tzinfo''


### Additional information

I assume its related to the fact that the device class need a datetime object but the integration only provides a string:

[](https://developers.home-assistant.io/docs/core/entity/sensor/#available-device-classes)
![image](https://github.com/user-attachments/assets/e8f5a6fa-e78a-4452-bc0a-e8e074f43fc5)
home-assistant[bot] commented 1 month ago

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (sensor) you are listed as a code owner for? Thanks!

Code owner commands Code owners of `sensor` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign sensor` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

(message by CodeOwnersMention)

sensor documentation sensor source (message by IssueLinks)

home-assistant[bot] commented 1 month ago

Hey there @gjohansson-st, @dougiteixeira, mind taking a look at this issue as it has been labeled with an integration (sql) you are listed as a code owner for? Thanks!

Code owner commands Code owners of `sql` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign sql` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

(message by CodeOwnersMention)

sql documentation sql source (message by IssueLinks)

gjohansson-ST commented 1 month ago

If you drop the isoformat() in your value template so it returns a datetime and not a string it should be fine.

Not sure actually what issue you're reporting as I guess you know what isoformat does and that it should be removed when sensor asks for a datetime?

Juchar commented 1 month ago

@gjohansson-ST the actual issue I am reporting is that I cannot create an SQL Entity of device class timestamp, even if removing isoformat(). It seems the SQL Integration always returns a String, no matter what.

Screenshot: image

Stacktrace:

Logger: homeassistant.components.sensor
Quelle: helpers/entity_platform.py:595
Integration: Sensor (Dokumentation, Probleme)
Erstmals aufgetreten: 07:31:05 (2 Vorkommnisse)
Zuletzt protokolliert: 07:34:26

Error adding entity sensor.ts_test_15 for domain sensor with platform sql
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 594, in state
    if value.tzinfo is None:
       ^^^^^^^^^^^^
AttributeError: 'str' object has no attribute 'tzinfo'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 595, in _async_add_entities
    await coro
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 909, in _async_add_entity
    await entity.add_to_platform_finish()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1365, in add_to_platform_finish
    await self.async_added_to_hass()
  File "/usr/src/homeassistant/homeassistant/components/sql/sensor.py", line 340, in async_added_to_hass
    await self.async_update()
  File "/usr/src/homeassistant/homeassistant/components/sql/sensor.py", line 353, in async_update
    self._process_manual_data(data)
  File "/usr/src/homeassistant/homeassistant/helpers/trigger_template_entity.py", line 234, in _process_manual_data
    self.async_write_ha_state()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1005, in async_write_ha_state
    self._async_write_ha_state()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1130, in _async_write_ha_state
    self.__async_calculate_state()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1067, in __async_calculate_state
    state = self._stringify_state(available)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1011, in _stringify_state
    if (state := self.state) is None:
                 ^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 605, in state
    raise ValueError(
ValueError: Invalid datetime: sensor.ts_test_15 has timestamp device class but provides state 2024-06-28 20:00:10:<class 'str'> resulting in ''str' object has no attribute 'tzinfo''
gjohansson-ST commented 1 month ago

I took a closer look as I couldn't find any issue with this. In your value template you have strptime(value.strip(), "%Y-%m-%d %H:%M:%S") but this does not specify any timezone in the format so you need to do strptime(value.strip(), "%Y-%m-%d %H:%M:%S%z") to also attach the timezone to the template output.