Unfortunately, the daily summary total is for 2023-09-17. We need 2023-09-16.
Rather than mess with unix epoch times, here is a simpler way to do it: *select **dateTime, date(dateTime,'unixepoch','localtime'), **sum(rain) from archive where date(dateTime,'unixepoch','localtime')=='2023-09-16';* *select dateTime, date(dateTime,'unixepoch','localtime') as dt, sum from archive_day_rain where dt=='2023-09-16';* The first query is not totally accurate because the record stamped 2023-09-16 00:00 is included, although it is actually part of the previous day. But, your totals are so far off, it should be good enough. On Mon, Jan 8, 2024 at 10:23 AM Ξ <[email protected]> wrote: > alright, I guess I misunderstood :) > > Here's the output adjusted for the local time zone: > > sqlite> select dateTime, datetime(dateTime, 'unixepoch', 'localtime'), > rain from archive where dateTime > 1694811638 and dateTime <= 1694898038; > > 1694812500|2023-09-16 00:15:00|0.0 > 1694813400|2023-09-16 00:30:00|0.0 > 1694814300|2023-09-16 00:45:00|0.0 > 1694815200|2023-09-16 01:00:00|0.0 > 1694816100|2023-09-16 01:15:00|0.0 > 1694817000|2023-09-16 01:30:00|0.0 > 1694817900|2023-09-16 01:45:00|0.0 > 1694818800|2023-09-16 02:00:00|0.0 > 1694819700|2023-09-16 02:15:00|0.0 > 1694820600|2023-09-16 02:30:00|0.8 > 1694821500|2023-09-16 02:45:00|2.4 > 1694822400|2023-09-16 03:00:00|3.6 > 1694823300|2023-09-16 03:15:00|0.4 > 1694824200|2023-09-16 03:30:00|0.0 > 1694825100|2023-09-16 03:45:00|0.0 > 1694826000|2023-09-16 04:00:00|0.4 > 1694827800|2023-09-16 04:30:00|4.4 > 1694828700|2023-09-16 04:45:00|0.0 > 1694830500|2023-09-16 05:15:00|4.4 > 1694831400|2023-09-16 05:30:00|0.0 > 1694832300|2023-09-16 05:45:00|0.0 > 1694834100|2023-09-16 06:15:00|4.4 > 1694835000|2023-09-16 06:30:00|0.0 > 1694835900|2023-09-16 06:45:00|0.0 > 1694836800|2023-09-16 07:00:00|0.0 > 1694837700|2023-09-16 07:15:00|0.4 > 1694838600|2023-09-16 07:30:00|0.4 > 1694839500|2023-09-16 07:45:00|0.4 > 1694840400|2023-09-16 08:00:00|0.0 > 1694841300|2023-09-16 08:15:00|0.4 > 1694842200|2023-09-16 08:30:00|0.4 > 1694843100|2023-09-16 08:45:00|0.4 > 1694844000|2023-09-16 09:00:00|0.0 > 1694846700|2023-09-16 09:45:00|8.4 > 1694850300|2023-09-16 10:45:00|9.2 > 1694851200|2023-09-16 11:00:00|0.0 > 1694853000|2023-09-16 11:30:00|9.2 > 1694853900|2023-09-16 11:45:00|0.0 > 1694854800|2023-09-16 12:00:00|0.0 > 1694855700|2023-09-16 12:15:00|0.0 > 1694856600|2023-09-16 12:30:00|0.0 > 1694857500|2023-09-16 12:45:00|0.0 > 1694858400|2023-09-16 13:00:00|0.0 > 1694859300|2023-09-16 13:15:00|0.0 > 1694860200|2023-09-16 13:30:00|0.0 > 1694861100|2023-09-16 13:45:00|0.0 > 1694862000|2023-09-16 14:00:00|0.0 > 1694862900|2023-09-16 14:15:00|0.0 > 1694863800|2023-09-16 14:30:00|0.0 > 1694864700|2023-09-16 14:45:00|0.0 > 1694865600|2023-09-16 15:00:00|0.0 > 1694866500|2023-09-16 15:15:00|0.0 > 1694867400|2023-09-16 15:30:00|0.0 > 1694868300|2023-09-16 15:45:00|0.0 > 1694869200|2023-09-16 16:00:00|0.0 > 1694870100|2023-09-16 16:15:00|0.0 > 1694871000|2023-09-16 16:30:00|0.0 > 1694871900|2023-09-16 16:45:00|0.0 > 1694872800|2023-09-16 17:00:00|0.0 > 1694873700|2023-09-16 17:15:00|0.0 > 1694874600|2023-09-16 17:30:00|0.0 > 1694875500|2023-09-16 17:45:00|0.0 > 1694876400|2023-09-16 18:00:00|0.0 > 1694877300|2023-09-16 18:15:00|0.0 > 1694878200|2023-09-16 18:30:00|0.0 > 1694879100|2023-09-16 18:45:00|0.0 > 1694880000|2023-09-16 19:00:00|0.0 > 1694880900|2023-09-16 19:15:00|0.0 > 1694881800|2023-09-16 19:30:00|0.0 > 1694882700|2023-09-16 19:45:00|0.0 > 1694884500|2023-09-16 20:15:00|9.2 > 1694885400|2023-09-16 20:30:00|0.0 > 1694886300|2023-09-16 20:45:00|0.0 > 1694887200|2023-09-16 21:00:00|0.0 > 1694888100|2023-09-16 21:15:00|0.0 > 1694889000|2023-09-16 21:30:00|0.0 > 1694889900|2023-09-16 21:45:00|0.0 > 1694890800|2023-09-16 22:00:00|0.0 > 1694891700|2023-09-16 22:15:00|0.0 > 1694892600|2023-09-16 22:30:00|0.0 > 1694893500|2023-09-16 22:45:00|0.0 > 1694894400|2023-09-16 23:00:00|0.0 > 1694895300|2023-09-16 23:15:00|0.0 > 1694896200|2023-09-16 23:30:00|0.0 > 1694897100|2023-09-16 23:45:00|0.0 > 1694898000|2023-09-17 00:00:00|0.0 > sqlite> > > sqlite> select dateTime, datetime(dateTime, 'unixepoch', 'localtime'), sum > from archive_day_rain where dateTime > 1694811638 and dateTime <= > 1694898038; > 1694898000|2023-09-17 00:00:00|0.8 > > sqlite> select dateTime, datetime(dateTime, 'unixepoch', 'localtime'), sum > from archive_day_rain where dateTime > 1694822400 and dateTime <= > 1694908800; > 1694898000|2023-09-17 00:00:00|0.8 > > On Sunday, January 7, 2024 at 11:13:32 PM UTC+2 Tom Keffer wrote: > >> Yes, I know you posted those, but we need to see the values in the daily >> summary for the same time periods. >> >> On Sun, Jan 7, 2024 at 1:11 PM Ξ <[email protected]> wrote: >> >>> I can pull data for December (tomorrow), but in the the first two posts >>> I've also posted excerpts from the db itself: >>> >>> https://groups.google.com/g/weewx-user/c/rIbaSfEgpFs/m/_HcbPFtXAQAJ >>> 2023-09-04 19:30:00 0.39999999 0.0 >>> 2023-09-04 19:45:00 0.05866666 0.0 >>> 2023-09-04 20:15:00 0.0 11.2 >>> 2023-09-04 20:30:00 0.0 0.0 >>> 2023-09-04 20:45:00 0.0 0.0 >>> >>> https://groups.google.com/g/weewx-user/c/rIbaSfEgpFs/m/sUmMTLNXAQAJ >>> 2023-09-16 09:45:00 1.00952380746438 8.4 >>> 2023-09-16 10:45:00 1.64067796275471 9.2 >>> 2023-09-16 11:00:00 0.42105263072000 0.0 >>> [....] >>> 2023-09-16 19:30:00 0.0 0.0 >>> 2023-09-16 19:45:00 0.0 0.0 >>> 2023-09-16 20:15:00 0.0 9.2 >>> 2023-09-16 20:30:00 0.0 0.0 >>> >>> Please notice in the last one how there's a sudden 9.2 value after hours >>> of no rain, in my view it's those bogus values that are discarded by >>> WU/PWS but they stay in the db. >>> >>> On Sunday, January 7, 2024 at 5:32:31 PM UTC+2 Tom Keffer wrote: >>> >>>> It's possible the errant values are in the daily summaries, but not the >>>> main archive table. The former is used for the NOAA reports, the latter for >>>> the WU posts. >>>> >>>> I don't know your timezone, but I imagine something like this: >>>> >>>> select dateTime, datetime(dateTime, 'unixepoch', 'localtime'), rain >>>> from archive where dateTime > 1694822400 and dateTime <= 1694908800; >>>> select dateTime, datetime(dateTime, 'unixepoch', 'localtime'), sum from >>>> archive_day_rain where dateTime > 1694822400 and dateTime <= 1694908800; >>>> >>>> -tk >>>> >>>> On Sat, Jan 6, 2024 at 6:32 PM Ξ <[email protected]> wrote: >>>> >>>>> Hello Tom, >>>>> >>>>> I've posted above a link to the exact date I was comparing: >>>>> https://www.wunderground.com/dashboard/pws/I5302/table/2023-09-16/2023-09-16/daily >>>>> You can see there on September 16, 2023 the total precipitation is >>>>> 9.14 mm whereas WeeWx is showing "Today's Rain: 59,2mm" >>>>> >>>>> Here's also the PWS link for the same date: >>>>> https://www.pwsweather.com/station/pws/dolniokol?timespan=day&date=2023-09-16 >>>>> It's showing a total of 9.1mm >>>>> >>>>> If we look for the month of December: >>>>> PWS shows total of 41.7mm >>>>> https://www.pwsweather.com/station/pws/dolniokol?timespan=month&date=2023-12-01 >>>>> WU shows a total of 41.67mm >>>>> https://www.wunderground.com/dashboard/pws/I5302/graph/2023-12-7/2023-12-7/monthly >>>>> >>>>> However if I look at the weewx report it's over 100mm: >>>>> >>>>> MONTHLY CLIMATOLOGICAL SUMMARY for Dec 2023 >>>>> NAME: Dolni Okol >>>>> ELEV: 1010 meters LAT: 42-29.40 N LONG: 023-29.70 E >>>>> >>>>> >>>>> TEMPERATURE (C), RAIN (mm), WIND SPEED (m/s) >>>>> >>>>> HEAT COOL AVG >>>>> MEAN DEG DEG WIND >>>>> DOM >>>>> DAY TEMP HIGH TIME LOW TIME DAYS DAYS RAIN SPEED >>>>> HIGH TIME DIR >>>>> >>>>> ---------------------------------------------------------------------------------------- >>>>> 01 9.4 14.7 13:30 -1.1 00:18 8.6 0.0 0.0 0.6 >>>>> 6.6 11:58 326 >>>>> 02 7.6 17.5 15:23 2.7 20:58 10.4 0.0 0.0 0.1 >>>>> 3.3 14:38 325 >>>>> 03 4.9 12.0 00:26 -2.0 23:56 13.1 0.0 13.2 0.1 >>>>> 3.3 13:02 33 >>>>> 04 -2.3 11.7 15:39 -6.3 07:48 20.3 0.0 0.0 0.0 >>>>> 2.3 03:53 117 >>>>> 05 -0.8 1.4 14:51 -2.5 07:23 18.8 0.0 0.0 0.0 >>>>> 1.4 11:35 303 >>>>> 06 0.7 1.3 14:03 -0.1 00:00 17.3 0.0 0.4 0.0 >>>>> 0.6 02:03 307 >>>>> 07 0.3 3.7 11:02 -2.0 23:16 17.7 0.0 0.0 0.1 >>>>> 3.0 13:48 56 >>>>> 08 -2.3 -0.9 13:18 -3.8 22:07 20.3 0.0 0.0 0.0 >>>>> 0.0 00:00 90 >>>>> 09 -2.1 1.0 14:38 -4.4 05:04 20.1 0.0 0.0 0.0 >>>>> 0.9 14:42 161 >>>>> 10 -0.8 10.6 15:17 -4.0 05:44 18.8 0.0 4.0 0.0 >>>>> 2.3 13:02 316 >>>>> 11 0.3 7.2 14:56 -3.6 07:52 17.7 0.0 0.8 0.0 >>>>> 2.0 12:04 138 >>>>> 12 3.8 13.6 15:35 0.1 21:58 14.2 0.0 0.8 0.0 >>>>> 3.0 05:10 14 >>>>> 13 3.2 11.1 14:28 0.4 04:02 14.8 0.0 0.0 0.0 >>>>> 1.2 15:36 302 >>>>> 14 5.7 11.7 10:36 0.6 02:20 12.3 0.0 7.2 0.1 >>>>> 3.1 04:20 41 >>>>> 15 1.1 2.1 03:06 -0.7 23:56 16.9 0.0 42.0 0.0 >>>>> 2.8 05:17 56 >>>>> 16 -2.0 -0.5 13:54 -3.7 23:59 20.0 0.0 0.0 0.0 >>>>> 0.0 00:00 90 >>>>> 17 -5.7 0.5 13:58 -10.5 07:32 23.7 0.0 0.0 0.0 >>>>> 0.0 00:00 90 >>>>> 18 -3.0 9.9 15:34 -8.1 01:40 21.0 0.0 18.8 0.0 >>>>> 0.0 00:00 90 >>>>> 19 -0.9 12.1 15:20 -4.6 07:04 18.9 0.0 4.8 0.0 >>>>> 0.0 00:00 90 >>>>> 20 1.4 9.2 15:04 -5.2 06:31 16.6 0.0 5.2 0.1 >>>>> 2.6 16:48 315 >>>>> 21 1.0 10.6 15:13 -3.5 20:04 17.0 0.0 0.8 0.1 >>>>> 2.5 22:09 47 >>>>> 22 1.9 3.7 03:57 0.1 23:01 16.1 0.0 4.0 0.2 >>>>> 4.4 00:41 320 >>>>> 23 2.4 5.3 11:23 -0.5 17:43 15.6 0.0 0.8 0.3 >>>>> 4.5 21:58 348 >>>>> 24 6.4 13.5 15:33 3.1 06:08 11.6 0.0 0.0 0.2 >>>>> 4.2 16:06 337 >>>>> 25 8.7 17.6 15:33 2.6 23:45 9.3 0.0 0.0 0.2 >>>>> 3.2 14:08 331 >>>>> 26 6.8 16.6 15:18 -0.3 05:13 11.2 0.0 0.0 0.1 >>>>> 2.2 09:18 330 >>>>> 27 4.3 16.0 15:26 -1.4 23:57 13.7 0.0 0.0 0.0 >>>>> 2.8 01:21 51 >>>>> 28 2.6 18.6 15:55 -2.7 07:19 15.4 0.0 0.0 0.1 >>>>> 2.3 14:41 169 >>>>> 29 2.7 15.1 15:49 -2.0 05:40 15.3 0.0 0.0 0.0 >>>>> 2.0 13:05 162 >>>>> 30 1.9 8.7 13:26 -2.4 04:34 16.1 0.0 0.0 0.1 >>>>> 2.7 14:40 35 >>>>> 31 2.3 15.5 15:20 -1.2 08:30 15.7 0.0 0.0 0.0 >>>>> 1.8 11:30 177 >>>>> >>>>> ---------------------------------------------------------------------------------------- >>>>> 2.0 18.6 28 -10.5 17 498.6 0.0 102.8 0.1 >>>>> 6.6 01 342 >>>>> >>>>> Even though I know nothing I have the gut feeling the WS6in1 driver >>>>> for Bresser doesn't handle well buffered data or the station itself passes >>>>> on buffered data wrongly (but in either case PWS and WU discard those >>>>> values)?! >>>>> >>>>> Also so sorry for the late reply here as well. >>>>> >>>>> Thanks, >>>>> Ivo >>>>> >>>>> On Thursday, September 21, 2023 at 2:45:20 AM UTC+3 Tom Keffer wrote: >>>>> >>>>> No. That's not enough clues to narrow it down. >>>>> >>>>> But, you say the "spike" is in the database, but not being uploaded to >>>>> PWS or Wunderground? Can you post your station ID for either/both? >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "weewx-user" 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-user/ff31b148-8915-4284-8585-b4e0cfdcf1c8n%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/weewx-user/ff31b148-8915-4284-8585-b4e0cfdcf1c8n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>> You received this message because you are subscribed to the Google >>> Groups "weewx-user" 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-user/7c4f63d8-6694-4b1b-9bc4-ddf0a2066001n%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/7c4f63d8-6694-4b1b-9bc4-ddf0a2066001n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > You received this message because you are subscribed to the Google Groups > "weewx-user" 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-user/723d0041-45b2-4a1a-8414-0f6a02487cc5n%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/723d0041-45b2-4a1a-8414-0f6a02487cc5n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "weewx-user" 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-user/CAPq0zEAbcXC-vVbZ%3DOJZDPOTtRwwhwHNpWY1XNvFtNnSk3KQHA%40mail.gmail.com.
