One query or two

Databases
1

Here is my query:

SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
     , round((AVG(`10714`) + AVG(`10773`) + AVG(`10788`) + AVG(`10803`) + AVG(`10817`) + AVG(`10847`) + AVG(`10891`) + AVG(`10906`) + AVG(`10936`))/9, 2) AS `2E low`
     , round((AVG(`11307`) + AVG(`11343`) + AVG(`11344`))/3, 2) AS `2F low`
     , round((AVG(`10964`) + AVG(`11023`) + AVG(`11053`) + AVG(`11067`) + AVG(`11097`) + AVG(`11126`))/6, 2) AS `2G low`
  FROM snr
WHERE
    updated > '2021-07-01 00:00:00' AND updated < '2021-07-31 23:59:59'
GROUP
    BY FLOOR(TIME_TO_SEC(TIME(updated))/600)

It creates a graph like this:

http://satellites-xml.org/signal-graphs/daily-trend-july-2021-graph.php

How can I make it output the data for 2 different months so I can superimposes them on the graph?

2

add this to the SELECT clause and without the alias to the GROUP BY clause –

EXTRACT(YEAR_MONTH FROM updated) AS YYYY-MM

and adjust the WHERE clause accordingly

WHERE updated > '2021-07-01 00:00:00' 
  AND updated < '2021-08-31 23:59:59'
3

Could you show me the complete query please?

4

…
my brain’s trying to tell me there’s a better way of doing that…too many function calls…why do we care about it being a Time at the end? Isn’t it just being rendered out as a string anyway?

5

sure

SELECT EXTRACT(YEAR_MONTH FROM updated) AS YYYY-MM
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
     , round((AVG(`10714`) + AVG(`10773`) + AVG(`10788`) + AVG(`10803`) + AVG(`10817`) + AVG(`10847`) + AVG(`10891`) + AVG(`10906`) + AVG(`10936`))/9, 2) AS `2E low`
     , round((AVG(`11307`) + AVG(`11343`) + AVG(`11344`))/3, 2) AS `2F low`
     , round((AVG(`10964`) + AVG(`11023`) + AVG(`11053`) + AVG(`11067`) + AVG(`11097`) + AVG(`11126`))/6, 2) AS `2G low`
  FROM snr
 WHERE updated > '2021-07-01 00:00:00' 
   AND updated < '2021-08-31 23:59:59'
GROUP
    BY EXTRACT(YEAR_MONTH FROM updated)
     , FLOOR(TIME_TO_SEC(TIME(updated))/600)
2 Likes
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.