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.
INT id DATETIME ts FLOAT temp FLOAT humidity FLOAT lux
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.