Mysql – Select range of non-identical date from different table

join;MySQLselect

On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between tables, as each sensor uploads at a different rate.

temp_data:

INT      id
DATETIME ts
FLOAT    temp
FLOAT    humidity
FLOAT    lux

power_data:

INT      id
DATETIME ts
FLOAT    kW
FLOT     volts

My question is:
I need to pull all rows within a certain date range from BOTH table- in this case, the last 12 hours, so, the most recent N rows, but in the future, any date range in the past. These will be sorted by the DATETIME value.

Using the (very good!) answer below, I get:

sourceTable id ts Ascending temp humd lux
power 19879 2015-05-16 18:13:08 0.5 121.9 NULL
temp 19880 2015-05-16 18:13:10 76 44 1099
power 19880 2015-05-16 18:13:12 0.51 122 NULL
temp 19881 2015-05-16 18:13:13 76 44 1102

…and so on. I need to preserve the column headers to parse this in JSON using PHP. Is there a way to do that? For instance, the volts/kW column headers are gone in this example. I could write some PHP which looks for a NULL in the third position or check the sourceTable field, so I'm considering this answer correct. However, I am interested to know if there's a way to keep the headers.

Best Answer

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point:

SELECT
  'temp' AS sourceTable,
  id,
  ts,
  temp AS measure1,
  humidity AS measure2,
  NULL AS measure3
FROM 
  temp_data
WHERE 
  ts >= @StartDate AND
  ts <= @EndDate
UNION ALL
SELECT
  'power' AS SourceTable,
  id,
  ts,
  lux AS Measure1,
  kw AS Measure2,
  volts AS Measure3
FROM 
  power_data
WHERE
  ts >= @StartDate AND
  ts <= @EndDate
Related Question