Getting the last value of the last day of the month from MySQL

Configuration
1

Hi,

I have my recorder setup with MySQL, but i’m a MySQL query noob. Can some MySQL guru’s help me out?

I found this example somewhere on this forum:

  - platform: sql
    queries:
      - name: testSQL
        query: "SELECT * FROM states WHERE entity_id = 'sensor.energy_import_total_maandelijks_kwh' ORDER BY ABS(LAST_DAY('2020-12-1')) LIMIT 1;"
        column: 'state'

I want to get the state value from the last day of the month, but it has to be the last value of that day. The query from above works, but it shows the first value of that day instead of the last value.

Thanks!!

2

No mysql guru’s? :innocent:

3

Got it working. For anyone who needs it:

  - platform: sql
    db_url: !secret db_mysql
    queries:
      - name: sql_kwh_maandelijks_december
        query: "SELECT * FROM states WHERE entity_id = 'sensor.energy_import_total_maandelijks_kwh' AND state != 'unknown' AND DATE(created) = LAST_DAY('2020-12-1') ORDER BY state DESC LIMIT 1;"
        column: 'state'
        unit_of_measurement: kWh