SQL information into timestamp sensor

[nilsreiter (Nils)]

Hi,

I’m trying to create sensors to display the last two times that a binary_sensor was on. (i.e., one sensor showing the timestamp of the last ‘on’ event, one sensor showing the timestamp of the second-to-last ‘on’-event). I figured that this info is already present in the database and just needs to be read, so I created an SQL sensor with the following query:

SELECT from_unixtime(states.last_updated_ts) as last_updated_ts FROM states, states_meta
      WHERE states_meta.entity_id in ("binary_sensor.haustur_opening")
      AND states.metadata_id = states_meta.metadata_id 
      AND states.state = "on"
    ORDER BY states.last_updated_ts DESC
    LIMIT 1;

As far as I can tell, this works well, and I could easily change the LIMIT to get the next value. The raw database output is fed into a value_template that looks like this: {{ (value|as_datetime|as_local).isoformat() }}

The sensor value I get from this is 2025-04-18T09:11:42.906999+02:00. As far as I know, this is exactly the ISO 8601 string representing the time stamp.

When I set the device_class of the sensor to timestamp, however, I get the following error message in the log:

Logger: homeassistant.components.sensor
Quelle: helpers/entity_platform.py:633
Integration: Sensor (Dokumentation, Probleme)
Erstmals aufgetreten: 14:12:21 (1 Vorkommnisse)
Zuletzt protokolliert: 14:12:21

Error adding entity sensor.zeitpunkt_der_letzten_hausturoffnung for domain sensor with platform sql
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 573, 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 633, in _async_add_entities
    await coro
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 972, in _async_add_entity
    await entity.add_to_platform_finish()
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1384, in add_to_platform_finish
    self.async_write_ha_state()
    ~~~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1023, in async_write_ha_state
    self._async_write_ha_state()
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1148, in _async_write_ha_state
    self.__async_calculate_state()
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1085, in __async_calculate_state
    state = self._stringify_state(available)
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1029, in _stringify_state
    if (state := self.state) is None:
                 ^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/sensor/__init__.py", line 584, in state
    raise ValueError(
    ...<2 lines>...
    ) from err
ValueError: Invalid datetime: sensor.zeitpunkt_der_letzten_hausturoffnung has timestamp device class but provides state 2025-04-18T09:11:42.906999+02:00:<class 'str'> resulting in ''str' object has no attribute 'tzinfo''

The sensor becomes unavailable.

I tried removing the milliseconds, but that didn’t change anything. Any ideas?

[123 (Taras)]

Try this version of the SQL query with the same Jinja2 template.

SELECT last_updated_ts FROM states, states_meta
WHERE states_meta.entity_id in ("binary_sensor.haustur_opening")
AND states.metadata_id = states_meta.metadata_id 
AND states.state = "on"
ORDER BY states.last_updated_ts DESC
LIMIT 1;

[nilsreiter (Nils)]

@123

SELECT last_updated_ts FROM states, states_meta
WHERE states_meta.entity_id in ("binary_sensor.haustur_opening")
AND states.metadata_id = states_meta.metadata_id 
AND states.state = "on"
ORDER BY states.last_updated_ts DESC
LIMIT 1;

Thanks, but this does not change anything. I get exactly the same error message.

[nilsreiter (Nils)]

Actually, even if I use a fixed string 2025-04-18T09:11:42+02:00 or {{ now().isoformat() }} as template, the same error message shows up in the log. This might be a bug.

[123 (Taras)]

Are you creating the SQL Sensor via the UI (as a SQL Sensor helper) or via YAML?

• If it’s via the UI, please post a screenshot.
• If it’s via YAML, please post the YAML code

[nilsreiter (Nils)]

I used the GUI (because it doesn’t need a HA restart):

Interface in German, sorry. But I think the contents should make it clear.

[123 (Taras)]

I can confirm that if I create the SQL Sensor with no device_class it reports a datetime string. However if I set its device_class to timestamp it reports unavailable.

There is an existing Issue that reports a similar problem.

Time zone info error when SQL device class set to timestamp

[nilsreiter (Nils)]

Ah cool, this seems to be the same problem. Thanks for finding it!