0

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.

  • 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. – Nicholai May 18 '15 at 12:52
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
  • 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. – Vérace Mar 26 '15 at 14:53
  • 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. – Nicholai Mar 26 '15 at 15:11
  • 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. – Vérace Mar 26 '15 at 15:13
  • please re-read the query. it is the first field, called SourceTable. – Nicholai Mar 26 '15 at 15:50
  • Sorry - my apologies - I missed that part of your SQL. Elegant approach! +1. – Vérace Mar 26 '15 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.