SQL sensor

Configuration
1

Made this SQL sensor for retrieve a specific year energy value, but I got an invalid SQL error when I submit it:

SELECT
(SELECT
statistics.sum
FROM
statistics
INNER JOIN statistics_meta ON
statistics.metadata_id=statistics_meta.id
WHERE
statistics_meta.statistic_id=‘sensor.house_energy_netto’
ORDER BY statistics.start_ts DESC
LIMIT 1)
-
(SELECT
statistics.sum
FROM
statistics
INNER JOIN statistics_meta ON
statistics.metadata_id=statistics_meta.id
WHERE
statistics_meta.statistic_id=‘sensor.house_energy_netto’
AND
start_ts > UNIXEPOCH(“2023-12-31 23:59:59”,“-1 YEAR”) AND end_ts < UNIXEPOCH(“2023-12-31 23:59:59”)
LIMIT 1)
AS size

I learned the SQL syntax from the community. If I omit the end_ts part, the query runs but counts up until today. I try to get the total of the year 2023.

Who can help me out to get this going?

2

I suggest you first try this out using DBBrowser of SQLLite, and do this on a COPY of the database to avoid damage.
EDIT: I donot see anything major but well… sqlite is a bit odd at times and so is setting up SQL sensor. If not already done, try to setup a very simple SQL sensor so you know that this works

3

What does it return when you only run the 2nd Sub-Select ? Does it return a value for 2023?

4

Maybe try to get rid of the start_ts part of the second query, basically replicating the first query sortign b end timestamp and getting the first record

...
AND
end_ts < UNIXEPOCH(“2023-12-31 23:59:59”)
ORDER_BY statistics.end_ts DESC
LIMIT 1)
5

Figured it out. This works:

SELECT
(SELECT
statistics.sum
FROM
statistics
INNER JOIN statistics_meta ON
statistics.metadata_id=statistics_meta.id
WHERE
statistics_meta.statistic_id=‘sensor.house_energy_netto’
AND
start_ts > UNIXEPOCH(“2023-12-31 23:23:59”)
LIMIT 1)
-
(SELECT
statistics.sum
FROM
statistics
INNER JOIN statistics_meta ON
statistics.metadata_id=statistics_meta.id
WHERE
statistics_meta.statistic_id=‘sensor.house_energy_netto’
AND
start_ts > UNIXEPOCH(“2023-01-01 00:00:00”)
LIMIT 1)
AS size