I am having two issues with using templates to dynamically set a datetime range for a RESTful sensor. I am calling a RESTful service provided by the City of Calgary that returns a value for the Air Quality Health Index.
I have it working when I have a static date range, but I really should be using a dynamic date range so that the latest readings are always retrieved.
My setup so far:
sensors.yaml
- platform: rest
resource: https://data.calgary.ca/resource/g9s5-qhu5.json
method: GET
params:
parameter: Fine Particulate Matter
abbreviation: 04AQM
$where: readingdate between '2023-06-16T01:00:00' and '2023-06-16T11:00:00'
# $where: readingdate between '2023-06-16T01:00:00' and '{{ now().strftime("%Y-%m-%dT%H:%M:%S") }}'
$order: readingdate DESC
name: Particulate Matter 2.5mm - Varsity
unique_id: pm25Varsity
value_template: "{{ value_json[0].value| int }}"
scan_interval: 3600
device_class: pm25
force_update: true
With the sensor setup as above, I am able to get the value for the AQHI. However, with I add a template to use the current datetime at the end of the where clause nothing is returned (the commented out $WHERE clase in the code above).
Here is what the results look like in Home Assistant:
Because of the unknown state when I am using the template for the end date, I am guessing that the end date is not rendering correctly in the sensor.yaml.
Any thoughts on what is wrong there?
My second issue is to do with time zones in the templates. When I convert the datetime to Epoch time, the template no longer has the datetime in my timezone. For the life of me, I can’t find how to get the template to use a timezone.
Any guidance on how to get the template to use a timezone?
$where: >
{%- set s = today_at(now().hour ~ ':00') %}
{%- set e = s + timedelta(hours=10) %}
{%- set f = "%Y-%m-%dT%H:%M:%S" %}
readingdate between '{{ s.strftime(f) }}' and '{{ e.strftime(f) }}'
There is still a problem. When I use the where clause using the Jinja template, the JSON returned from the rest api is empty ([]). If I hard code to start and end dates, then the template returns the expected value.
The actual error message is: Template variable warning: 'dict object' has no attribute 'value' when rendering '{{ value_json[0].value }}'
The message makes sense, because the JSON returned has no text.
So it seems to be something with the dynamic where clause, but I don’t have enough information to know for sure.
Is there a way to see the entire URL that is being called from the rest sensor? That might provide some hints as to why nothing is being returned.
Sorry, I did not explain myself well enough in my previous post.
The issue is that the JSON returns no values [ ] when I use the template to set the dates. When I hard code the dates in the where clause, data is returned.
This makes me think that there is something wrong with the URL, and the $WHERE clause.
I was wondering if there was a way to see the entire URL that is being called so that I could make sure it is correct. I could not find the URL in the logs with debit on.
You can try to ensure there’s no whitespace with the following template, other than that, I’m not sure what to tell you. You can turn on debugging and it might output the resource.
$where: >
{%- set s = today_at(now().hour ~ ':00') -%}
{%- set e = s + timedelta(hours=10) -%}
{%- set f = "%Y-%m-%dT%H:%M:%S" -%}
readingdate between '{{ s.strftime(f) }}' and '{{ e.strftime(f) }}'
Thanks for your help, but it is still not working as expected. I have turned on all the debugging that I could find for the logs, and I have not been able to see the full URL. The search continues…
Turns out the issue appears to be data related. The API does not always provide a value when the readings are zero. In fact, the entire tag value: is missing from the JSON returned.
My hacky work around is to get the readings for the last 4 hours, and to set the value of the sensor to zero when the value is not found.
- platform: rest
resource: https://data.calgary.ca/resource/g9s5-qhu5.json
method: GET
params:
parameter: Fine Particulate Matter
abbreviation: 04AQM
$where: >
{%- set endTime = now() %}
{%- set startTime = endTime - timedelta(minutes=90) %}
{%- set format = "%Y-%m-%dT%H:%M:%S" %}
readingdate between '{{ startTime.strftime(format) }}' and '{{ endTime.strftime(format) }}'
$order: readingdate DESC
name: Particulate Matter 2.5mm - Varsity
unique_id: pm25Varsity
value_template: >
{% if value_json[0].value is defined %}
{{ value_json[0].value }}
{% else %}
0
{% endif %}
json_attributes:
- readingdate
- station_name
- abbreviation
- parameter
- units
- units_description
- latitude
- longitude
scan_interval: 1800
device_class: pm25
force_update: true
I will mark your earlier post with the template as the solution…
