Select range of non-identical date from different table

Question

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.

• Nicholai: Regarding your edit, it isn't possible for SQL to have a result set where a single column has more than one identifier. You would either check the sourceTable field in your PHP and have some logic there, or run multiple queries.

Answer 1

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

• Vérace: I was thinking the same thing as @Nicholai, but what you really need is some way of distinguishing Temp_Data from Power_Data - create an extra field on both tables - call it Rec_Type SMALLINT - 1 = Temp_Data, 2 = Power_Data.
• Nicholai: the table name tells us what type of data it is, we don't need to create a column that is the same for every row in that table. we do that with the first field in each select.
• Vérace: No - when you UNION the results, you'll need to be able to tell if the record is a Temp or a Power record - that data will be lost with the UNION.
• Nicholai: please re-read the query. it is the first field, called SourceTable.
• Vérace: Sorry - my apologies - I missed that part of your SQL. Elegant approach! +1.