Output statistical data from yesterday into CSV file

Configuration
1

Hi,

I’d like to export specific sensor data as daily values in CSV-format in a file.

Following sequence:

  • first day in a month: rename old output file, crate a new one
  • daily write a number of sensor data based on previous day in CSV Format into file
    e.g.
    • average daily outdoor temperature
    • max daily outdoor temperature
    • min daily outdoor temperature
    • …

It’s simple to remotely access the HA DB, create a few SQL-queries to get those numbers. But, I’d like to get it done based on HA mechanisms.

So far I tried different things like helpers, templates, etc.. Unfortunately, all those mechanisms seem, don’t look straight forward to accomplish those simple(?) things.

Is there a simple way available on HA, or should I stick to my remote DB access approach?

Fred

2

If this is all weather based then I suggest a left field approach like I do.

Publish your weather data to MQTT and then use Weewx to generate your Website, Data, Whatever using Skins that are available.

e.g. https://carlingfordweather.sydney

  1. Data is a mix of a) data retrieved from a data logger via C# wrapped C library fed into Home Assistant via AppDaemon script b) Ecowitt wind sensor directly in Home Assistant
  2. I use the fuzzy archer template (see ABOUT on website) which enables the real time gauges and graphs. Weewx plot/graph system generates the 24 hours, Week, Month, Year plots

Most Weewx skins have NOAA data generators which is probably closest to what you desire. I have mine linked in on the history page. I have history going back to 2015 - I used Weewx prior to Home Assistant but now Home Assistant is the master of the data and Weewx uses the MQTT subscriber driver to get data that Home Assistant publishes to MQTT.

Example text NOAA report from June 2026 
                   MONTHLY CLIMATOLOGICAL SUMMARY for Jun 2026


NAME: Carlingford Weather                  
ELEV: 114 metres    LAT: 33-46.30 S    LONG: 151-02.22 E


                   TEMPERATURE (C), RAIN (mm), WIND SPEED (km/h)

                                         HEAT   COOL         AVG
      MEAN                               DEG    DEG          WIND                   DOM
DAY   TEMP   HIGH   TIME    LOW   TIME   DAYS   DAYS   RAIN  SPEED   HIGH   TIME    DIR
---------------------------------------------------------------------------------------
 01   11.8   16.1  15:02    8.3  07:44    6.5    0.0    0.0    3.5   22.3  11:28    349
 02   13.3   16.0  11:18   10.1  05:40    5.1    0.0    4.1    6.1   31.7  15:55    355
 03   15.5   19.5  13:23   12.6  23:17    2.8    0.0    0.0    7.6   49.3  13:23    316
 04   13.8   18.0  13:26   10.4  23:45    4.5    0.0    0.2    6.1   32.0  19:25    326
 05   13.4   17.7  13:52   10.2  06:24    5.0    0.0    0.0    5.3   37.1  10:59    236
 06   11.7   17.6  15:06    7.5  06:50    6.6    0.0    0.0    3.5   17.6  10:33    137
 07   11.8   17.6  14:18    7.4  06:54    6.5    0.0    0.0    3.1   12.2  09:23     72
 08   13.1   18.6  13:44    8.8  07:15    5.3    0.0    0.0    3.0   14.8  13:34     67
 09   14.6   19.9  13:48   10.3  07:34    3.7    0.0    0.0    3.2   17.3  16:18     85
 10   16.0   21.4  14:18   11.0  06:45    2.3    0.0    0.0    3.1   18.4  13:11     59
 11   16.9   19.9  14:16   15.0  07:52    1.4    0.0    0.9    2.9   14.4  13:58    105
 12   16.7   20.1  13:09   14.4  23:30    1.6    0.0    0.0    2.7   15.8  11:57    121
 13   16.8   21.6  13:53   13.3  07:09    1.5    0.0    0.0    2.6   14.4  10:34     52
 14   16.5   20.1  15:00   14.5  23:33    1.8    0.0    3.1    3.5   20.2  06:49     80
 15   15.3   18.2  12:27   13.8  23:16    3.1    0.0    0.0    2.9   12.6  11:24    107
 16   15.1   19.8  14:36   11.3  06:26    3.3    0.0    0.0    3.0   19.4  11:36     45
 17   15.1   19.1  13:44   12.7  07:19    3.2    0.0    0.0    2.5   11.9  11:28     56
 18   16.1   19.8  13:28   12.1  05:34    2.2    0.0    0.0    3.9   23.4  22:45     20
 19   17.5   20.6  12:59   14.2  23:58    0.8    0.0    0.0    7.2   41.0  09:29    336
 20   15.0   19.6  14:58   11.9  05:38    3.3    0.0    0.0    4.6   27.4  15:14    337
 21   12.6   17.7  14:39    9.3  06:59    5.7    0.0    0.0    3.6   25.9  13:30     53
 22   11.7   17.0  14:15    8.2  07:03    6.6    0.0    0.0    3.4   18.4  12:35     99
 23   10.7   16.3  14:06    7.0  06:55    7.6    0.0    0.0    3.7   23.0  09:32     98
 24   11.2   17.5  13:54    6.4  07:08    7.1    0.0    0.0    3.7   18.0  12:11    102
 25   12.0   14.3  14:02   10.0  04:44    6.4    0.0    2.0    2.9   15.1  15:00    138
 26   12.0   14.4  13:36   10.6  07:30    6.3    0.0    0.6    2.2    9.7  15:43    120
 27   11.8   15.8  14:24    8.9  04:36    6.5    0.0    0.4    2.3   10.1  11:01     90
 28   12.5   16.1  12:49   10.6  04:43    5.9    0.0    2.5    2.1   14.0  13:08    102
 29   12.6   17.1  14:16   10.3  03:41    5.7    0.0    0.0    2.3    9.7  13:52     62
 30   13.2   16.4  14:17    9.7  07:54    5.1    0.0    1.5    3.4   20.2  18:17     77
---------------------------------------------------------------------------------------
      13.9   21.6     13    6.4     24  133.5    0.0   15.3    3.7   49.3     03     33

The good part of this approach is that Weewx handles the weather data with no crafting of anything in Home Assistant except to publish to MQTT in a format ready for the Weewx MQTT subscriber driver to pull.

Extras?

  • Weewx schema can be adjusted so you can bake in any weather related data which is not in its usual schema
  • skins can generate any text and plots so the sky is the limit without what would be lots of automations in Home Assistant.
3

If you want to do this in native HA it is fairly straightforward. There is an action recorder.get_statistics that will retrieve whatever statistics data you are wanting. You can choose the date range, the period (e.g. day) and the statistics types (e.g. max, min, and mean).

If you go to settings → developer tools → actions, you can try it out and see what it does.

Then you can perform this action in an automation that is triggered daily, and use templates to format the output however you’d like.

To write to a file, first set up a file notification service (you can also use shell commands as a more advanced method). Go to settings → devices and services → add integration → search for “file” → choose “set up a notification service”

Once that is configured, you can use the action notify.send_message to that new action and it will add a line to that file.

For monthly renaming I’d probably use Shell commands.

4

Thumbs up to Darryn’s idea, it’s probably the simplest you can get.

The way you wanted it is also doable but requires more efforts.

  1. outputting text to a file can be done via file integration

  2. file renaming can be done via shell command integration, e.g.

mv: "/bin/mv -f \"{{src}}\" \"{{dst}}\""

Usage:

- variables:
    # produces, e.g. 2026-07.csv when ran on the first day of August
    new_file_name: "{{ (now() - timedelta(days=1)).strftime("%Y-%m") }}.csv"
- action: shell_command.mv
  data:
    src: "/config/my_csv/current.csv"
    dst: "/config/my_csv/{{ new_file_name }}"
  response_variable: reply
- variables:
    success: "{{ reply.get('returncode') == 0 }}"
- if: "{{ success }}"
  then:
    [...]
5

I was going to suggest the File integration, too.

I use that in a bunch of automations, to keep various log files in CSV format.

I also use command-line in automations to rename the files monthly.

6

this is really a very helpful and fast reacting community!

As I intend collecting various data items - not only weather data - Ricks and slimaks hints are the ones I was aiming for. I just tested the recorder.get_statistics feature; it works perfect.

Thanks a lot for your help!
Fred