Welcome to the Cumulus Support forum.

Latest Cumulus MX release 3.28.5 (build 3282) - 23 February 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

MySQL Monthly Table

General news concerning announcements of new releases, forum outages etc.
Post Reply
wbargent
Posts: 8
Joined: Wed 02 Mar 2016 7:37 pm
Weather Station: LaCrosse WS2300, Oregon WMR88/89
Operating System: Windows Server 2012 R2
Location: UK
Contact:

MySQL Monthly Table

Post by wbargent »

Hi, I have a question about the SQL Monthly table. I currently have my station reporting every 5 mins and this is then logged into a monthly table on my MySQL Server however I would like to know what happened when the month is over. Is a new table created?
User avatar
steve
Cumulus Author
Posts: 26702
Joined: Mon 02 Jun 2008 6:49 pm
Weather Station: None
Operating System: None
Location: Vienne, France
Contact:

Re: MySQL Monthly Table

Post by steve »

No, it all goes into the same table.
Steve
wbargent
Posts: 8
Joined: Wed 02 Mar 2016 7:37 pm
Weather Station: LaCrosse WS2300, Oregon WMR88/89
Operating System: Windows Server 2012 R2
Location: UK
Contact:

Re: MySQL Monthly Table

Post by wbargent »

So what is the difference between the monthly and daily table?
Could I suggest this to be a feature in a later version? Table for each month.
Last edited by wbargent on Thu 03 Mar 2016 9:23 pm, edited 1 time in total.
duke

Re: MySQL Monthly Table

Post by duke »

wbargent wrote: Could I suggest this to me a feature in a later version? Table for each month.
Why?
User avatar
philpugh
Posts: 376
Joined: Tue 24 May 2011 8:34 am
Weather Station: See Signature
Operating System: Debian 11 (RPi4)
Location: Antrobus, Cheshire, UK
Contact:

Re: MySQL Monthly Table

Post by philpugh »

You will find that it is much easier to do analysis/summary work on the whole table. That said I also make a copy of the data into yearly tables.
Phil Pugh
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
User avatar
steve
Cumulus Author
Posts: 26702
Joined: Mon 02 Jun 2008 6:49 pm
Weather Station: None
Operating System: None
Location: Vienne, France
Contact:

Re: MySQL Monthly Table

Post by steve »

wbargent wrote:So what is the difference between the monthly and daily table?
The monthly table contains the minute-by-minute data corresponding to the monthly log files in MX (Mar16log.txt etc), and the daily table contains the daily summary data corresponding to the dayfile.txt file.
Could I suggest this to be a feature in a later version? Table for each month.
It's all in one table to make queries easier. MX will use the table itself at some point, so I won't be changing that. It's possible there might eventually be an option to create an additional table for each month, but I'm not sure of the benefits.
Steve
User avatar
mcrossley
Posts: 12529
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL Monthly Table

Post by mcrossley »

I can't see any possible benefits either. Splitting the data into tables would make handling it much more difficult in most instances.

If you really wanted to see month by month data without adding a where clause, then you could always create a view for each month.
wbargent
Posts: 8
Joined: Wed 02 Mar 2016 7:37 pm
Weather Station: LaCrosse WS2300, Oregon WMR88/89
Operating System: Windows Server 2012 R2
Location: UK
Contact:

Re: MySQL Monthly Table

Post by wbargent »

The main reason my I wan't to do this was due to the number of records that get inserted into my table; 1 every 5 mins. I thought it would be a good way to split the data up into smaller tables (not that they will they will be small) making them more manageable and easier to locate records using an application like phpmyadmin or Toad.
User avatar
mcrossley
Posts: 12529
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL Monthly Table

Post by mcrossley »

The table is indexed on the date/time by default, so even with millions of records retrieving the date range you want should be fast.

My table only goes back to Jan 2010 with approx 500,000 rows, extracting the records for Jan 2016...
(8922 total, Query took 0.0023 sec)
Post Reply