...
| sensor_id | year | field | count | sum | average | start_time | end_time | updated |
|---|---|---|---|---|---|---|---|---|
| 12345 | 2003 | temperature | 120 | 8400.0 | 60.0 | |||
| 12345 | 2003 | pH | 120 | 240.0 | 2.0 |
- are start/end times for bins actually useful for anything? there could be holes in between endpoints
- store completeness by sensor /stream?
bins_month
| sensor_id | month | year | field | count | sum | average | start_time | end_time | updated |
|---|---|---|---|---|---|---|---|---|---|
| 12345 | 6 | 2003 | temperature | 10 | 600.0 | 60.0 | |||
| 12345 | 6 | 2003 | pH | 10 | 20.0 | 2.0 |
...
bins_special (alt option)
...
| label | fieldstart_time | end_time | updated | |||
|---|---|---|---|---|---|---|
| 12345 | spring | temperature | Jan 1 | Mar 31 | 12345||
| spring | pH | Jan 1 | Mar 31 |
- if i want special bin by year, only consider points between start and end time.
- if start/end time includes entire year, use bins_year
- if < 1 year time span, aggregate month + day bins until you cover entire time span
- if i want by months, include each month between start/end time
- for complete months, use bins_month
- for partial months, aggregate day bins until you cover entire time span
...
- cron job (hourly? 5 minutes?)
- whenever new datapoint is added (at most 1 bin per table would need to be created or updated) - upsert
Trends region
