Welcome to the new home of the Cumulus Support forum.

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3044 7 December 2018. See the Wiki for download

MySQL query question

Other discussion about creating web sites for Cumulus that doesn't have a specific subforum

Moderator: daj

Post Reply
kapo
Posts: 247
Joined: Thu 03 Jan 2013 1:59 pm
Weather Station: Davis VP2
Operating System: Windows 10
Location: Vihtavuori, Laukaa, Finland

MySQL query question

Post by kapo » Sat 27 Dec 2014 6:39 pm

Hi
I have db Monthlylog and there table "Monthly". I can make queries as: $query = "SELECT LogDateTime, max(Temp) as maxTemp, max(Dewpoint) as maxDewpoint, FROM Monthly WHERE LogDateTime > '2012-12-18 18:20:00'";
So I can make queries as min, max, avg etc. But how can I get timestamps/dates to those vals, for example: Max. Temp 30,9C @ 2014-06-07? So would someone be so friendly and give me some advice in this? As how to?


regards: Kapo

User avatar
mcrossley
Posts: 5232
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL query question

Post by mcrossley » Sat 27 Dec 2014 8:02 pm

You need to 'group by' the interval of interest. The daily min/max/avg temp are already done for you of course in the daily file/table.

kapo
Posts: 247
Joined: Thu 03 Jan 2013 1:59 pm
Weather Station: Davis VP2
Operating System: Windows 10
Location: Vihtavuori, Laukaa, Finland

Re: MySQL query question

Post by kapo » Sun 28 Dec 2014 5:42 am

Thank You about your answer. Yes I know those other daily max, min... values. And there are also all kind of max, min, avg values too inside my pages... But the thing I ask this is that I want to learn more about MySQL use... My favorit pages are W3Schools online Web Tutorials, and from there I have learnd some little things. Is it possible to have a little example of that 'to group by' query? If not, that's okay, I understand that too.


regards Kapo

User avatar
mcrossley
Posts: 5232
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL query question

Post by mcrossley » Mon 29 Dec 2014 12:00 am

Sorry reading your first post again it sees you are just after the max or min or avg of some values over a range of dates? If that is the case, then if you also want the times of each one then you just have to also select the logdatetime as you have done. The gotcha is that if you want the date/time then you will have to specify a single value for each query. The aggregate functions Max min etc only return a single row, the date time for each will be different, so you have to put them in separate queries. (It may be possible to do it in a single query using some fancy joins and or sub-queries, but the base query should be really fast, just run it a number of times. My top 10s page runs 20-30 queries in sequence in under a second on my old 'puter)

Karv
Posts: 38
Joined: Sun 16 Dec 2012 11:19 pm
Weather Station: WH-1080
Operating System: Windows XP / 7, Ubuntu, Centos
Location: South Gloucestershire
Contact:

Re: MySQL query question

Post by Karv » Mon 29 Dec 2014 12:33 pm

something like :
$query = "SELECT b.LogDateTime,max(a.Temp) as maxTemp, max(a.Dewpoint) as maxDewpoint FROM Monthly a right join Monthly b using (LogDateTime) WHERE a.LogDateTime > '2012-12-18 18:20:00'";

I don't have this table at hand and am at work, so this is very theoretical but will hopefully provide insight into how to join a table to itself to get "both" results in one go.

Cheers
K
Image

User avatar
mcrossley
Posts: 5232
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL query question

Post by mcrossley » Mon 29 Dec 2014 1:17 pm

Not quite, because in that example you need to to select logdatetime twice, once for the occurrence of max(temp) and again for the occurrence of max(dewpoint).

Karv
Posts: 38
Joined: Sun 16 Dec 2012 11:19 pm
Weather Station: WH-1080
Operating System: Windows XP / 7, Ubuntu, Centos
Location: South Gloucestershire
Contact:

Re: MySQL query question

Post by Karv » Mon 29 Dec 2014 1:21 pm

yes, but then LogDateTime probably isn't even the best thing to use as the join either. :)

If someone can give me 30 or so lines from their table (ideally a dump file) I'll set it up here and have a play as its so quiet.

I'd probably opt for individual max / min queries to remove any ambiguity anyway, or a daily script to set the values for the month or something.
Image

kapo
Posts: 247
Joined: Thu 03 Jan 2013 1:59 pm
Weather Station: Davis VP2
Operating System: Windows 10
Location: Vihtavuori, Laukaa, Finland

Re: MySQL query question

Post by kapo » Mon 29 Dec 2014 3:21 pm

Thank You
I got what I wanted.


regards: Kapo

Post Reply