Simon Simon - 3 months ago 11
MySQL Question

MySQL Query on two tables containing different dates

I've a little issue I cannot handle very well.
I need to export some data from two tables. Both tables are containing only two fields. The first is a datetime, the second contains values.
I would like to graph those data grouping it by dates. Let me explain with an example.

Table A:

SELECT * FROM tableA order by Time desc limit 10;


output:

2016-08-26 12:50:10 27
2016-08-26 12:45:02 26.9
2016-08-26 12:40:04 26.8
2016-08-26 12:35:03 26.5
2016-08-26 12:30:03 27
2016-08-26 12:25:10 27.1
2016-08-26 12:21:28 27.2
2016-08-26 12:20:58 26.9
2016-08-26 12:20:28 27
2016-08-26 12:20:00 26.9


Table B:

SELECT * FROM tableB order by Time desc limit 10;

2016-08-26 12:56:03 30.1
2016-08-26 12:50:10 29.9
2016-08-26 12:45:02 29.8
2016-08-26 12:42:05 30.1
2016-08-26 12:35:04 30.1
2016-08-26 12:31:03 30
2016-08-26 12:25:11 30
2016-08-26 12:20:01 29.6
2016-08-26 12:17:01 29.6
2016-08-26 12:10:02 29.5


As you can see the Time value is not equal. I would like to get values taken from both tables, group and graph them. The graph is not a problem. I would like, in example, take one value per table every 5 minutes.
Any help would be apreciated!

Thanks!

Simon

Answer

Grouping to 5 minutes (300 seconds). If multiple values fall into one interval take the maximum value.

select  from_unixtime(U*300) as date,
        max(if(T='A',val,NULL)) as A_VAL,
        max(if(T='B',val,NULL)) as B_VAL
  from (
    select ceil(unix_timestamp(date)/300) as U, val, 'A' as T
      from tableA
      WHERE date BETWEEN '2016-08-26 12:20:00' AND '2016-08-26 12:55:00'
    union all
    select ceil(unix_timestamp(date)/300) as U, val, 'B' as T
      from tableB
      WHERE date BETWEEN '2016-08-26 12:20:00' AND '2016-08-26 12:55:00'
   ) A
 group by U
 order by U

Result:

2016-08-26 12:15:00 NULL    29.5
2016-08-26 12:20:00 26.900  29.6
2016-08-26 12:25:00 27.200  29.6
2016-08-26 12:30:00 27.100  30.0
2016-08-26 12:35:00 27.000  30.0
2016-08-26 12:40:00 26.500  30.1
2016-08-26 12:45:00 26.800  30.1
2016-08-26 12:50:00 26.900  29.8
2016-08-26 12:55:00 27.000  29.9
2016-08-26 13:00:00 NULL    30.1

Sample on sqlfiddle.com