Please read the posts in the Announcements section about the current status of Cumulus development now that I have retired

Please read this post before posting

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3043 Jan 20 2017. See this post for download

MySQL query question

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

Moderators: daj, TNETWeather

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

MySQL query question

Postby kapo » Sat Dec 27, 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: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL query question

Postby mcrossley » Sat Dec 27, 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: 246
Joined: Thu Jan 03, 2013 1:59 pm
Weather Station: Davis VP2
Operating System: Windows 10
Location: Vihtavuori, Laukaa, Finland

Re: MySQL query question

Postby kapo » Sun Dec 28, 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: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL query question

Postby mcrossley » Mon Dec 29, 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 Dec 16, 2012 11:19 pm
Weather Station: WH-1080
Operating System: Windows XP / 7, Ubuntu, Centos
Location: South Gloucestershire
Contact:

Re: MySQL query question

Postby Karv » Mon Dec 29, 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: 5192
Joined: Thu Jan 07, 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL query question

Postby mcrossley » Mon Dec 29, 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 Dec 16, 2012 11:19 pm
Weather Station: WH-1080
Operating System: Windows XP / 7, Ubuntu, Centos
Location: South Gloucestershire
Contact:

Re: MySQL query question

Postby Karv » Mon Dec 29, 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: 246
Joined: Thu Jan 03, 2013 1:59 pm
Weather Station: Davis VP2
Operating System: Windows 10
Location: Vihtavuori, Laukaa, Finland

Re: MySQL query question

Postby kapo » Mon Dec 29, 2014 3:21 pm

Thank You
I got what I wanted.


regards: Kapo


Return to “Web site - General”

Who is online

Users browsing this forum: BCJKiwi, dazza1223 and 3 guests