I think that would give the same answer as the unmodified version. You
probably want something like
SELECT SUM(`wsum`), SUM(`sumtime`) FROM {table}_day_{obs_type} "
"WHERE STRFTIME('%m-%d', dateTime, 'unixepoch','localtime') =
'{month:02d}-{day:02d}' "
"AND STRFTIME('%Y', dateTime, 'unixepoch','localtime') < {year:04d};"
where "year" is the present year. Alternatively, you could compute the unix
epoch time of a year ago (in Python), then use
SELECT SUM(`wsum`), SUM(`sumtime`) FROM {table}_day_{obs_type} "
"WHERE STRFTIME('%m-%d', dateTime, 'unixepoch','localtime') =
'{month:02d}-{day:02d}' "
"AND dateTime <= {time_a_year_ago};"
where "time_a_year_ago" is the unix epoch time a year ago. This has the
advantage that the second WHERE clause can use the fact that dateTime is
indexed.
Needless to say, none of this has been tested!
-tk
On Thu, Apr 23, 2020 at 6:36 PM rich T <[email protected]> wrote:
> Just so I'm clear, the historical data also contains data for today. If
> that is true and I want get the data from the beginning of the database to
> the current day in the previous year, I would change the following:
>
> historical_avg': "SELECT SUM(`wsum`), SUM(`sumtime`) FROM
> {table}_day_{obs_type} "
> "WHERE STRFTIME('%m-%d',
> dateTime,'unixepoch','localtime','-1 year') = '{month:02d}-{day:02d}';"
>
> Reason I'm asking, if I take my previous 10 years of data minus current
> daily temperature average I can determine if the current day temperature is
> above or below normal for my station.
>
> Thanks for all the time and efforts that everyone puts into this great
> software.
>
>
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "weewx-development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/weewx-development/262a6a6a-baf9-42a9-9873-30daf55e31a1%40googlegroups.com
> <https://groups.google.com/d/msgid/weewx-development/262a6a6a-baf9-42a9-9873-30daf55e31a1%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
You received this message because you are subscribed to the Google Groups
"weewx-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/weewx-development/CAPq0zED-yLcYdW59WFso_uMt0rqv7ax3vqX5Tosug%2BDmxFN0ew%40mail.gmail.com.
