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
Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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 query question
Moderator: daj
- mcrossley
- Posts: 12765
- 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 query question
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.
-
- Posts: 246
- Joined: Thu 03 Jan 2013 1:59 pm
- Weather Station: Davis VP2
- Operating System: Windows 10
- Location: Vihtavuori, Laukaa, Finland
Re: MySQL query question
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
regards Kapo
- mcrossley
- Posts: 12765
- 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 query question
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)
-
- Posts: 40
- Joined: Sun 16 Dec 2012 11:19 pm
- Weather Station: WH-1080
- Operating System: Windows XP / 7, Ubuntu, Centos
- Location: South Gloucestershire
Re: MySQL query question
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
$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
- mcrossley
- Posts: 12765
- 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 query question
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).
-
- Posts: 40
- Joined: Sun 16 Dec 2012 11:19 pm
- Weather Station: WH-1080
- Operating System: Windows XP / 7, Ubuntu, Centos
- Location: South Gloucestershire
Re: MySQL query question
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.
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.
-
- Posts: 246
- Joined: Thu 03 Jan 2013 1:59 pm
- Weather Station: Davis VP2
- Operating System: Windows 10
- Location: Vihtavuori, Laukaa, Finland
Re: MySQL query question
Thank You
I got what I wanted.
regards: Kapo
I got what I wanted.
regards: Kapo