0

I have a temperature table like this

|d_id   |d_l_id |d_temperature |d_humidity |d_log_date          |
|1      |1      |29.80         |23.40      |2020-02-29 00:00:01 |
|2      |1      |29.80         |23.40      |2020-02-29 00:00:02 |
|3      |1      |30.00         |23.90      |2020-02-29 00:00:03 |
|4      |1      |30.00         |23.90      |2020-02-29 00:00:04 |
|5      |1      |30.00         |23.90      |2020-02-29 00:00:05 |
|6      |1      |30.00         |23.90      |2020-02-29 00:00:06 |
|7      |1      |29.80         |23.40      |2020-02-29 00:30:51 |
|8      |1      |30.00         |23.90      |2020-02-29 00:30:52 |
|9      |1      |30.00         |23.90      |2020-02-29 00:30:53 |
|10     |1      |29.70         |23.40      |2020-02-29 00:30:54 |

and I'm avg with this sql

SELECT count(*) rec_count, avg(d_temperature) avg_temp, avg(d_humidity) avg_hum, d_log_date FROM its.data
where d_l_id = 1
GROUP BY (UNIX_TIMESTAMP(d_log_date)) DIV 30;

and getting this result

|6  |30.645455 |27.381818 |2020-02-29 00:00:01|
|4  |30.579311 |27.306896 |2020-02-29 00:01:51|

Sometimes device lost connection and device is not recording. But I need to list like this. How can I do that.

|6  |30.645455 |27.381818 |2020-02-29 00:00:01|
|0  |null or 0 |null or 0 |2020-02-29 00:00:30|   <<
|0  |null or 0 |null or 0 |2020-02-29 00:01:00|   <<
|0  |null or 0 |null or 0 |2020-02-29 00:01:30|   <<
|0  |null or 0 |null or 0 |2020-02-29 00:02:00|   <<
 .   .          .          .                      <<
 .   .          .          .                      <<
|0  |null or 0 |null or 0 |2020-02-29 00:29:00|   <<
|0  |null or 0 |null or 0 |2020-02-29 00:29:30|   <<
|0  |null or 0 |null or 0 |2020-02-29 00:30:00|   <<
|0  |null or 0 |null or 0 |2020-02-29 00:30:30|   <<
|4  |30.579311 |27.306896 |2020-02-29 00:30:51|
Hakkı ÜN
  • 1
  • 1
  • Your results do not relate to your sample data (even your desired results do not seem consistent with your current results), so it is not easy to understand what you actually want. – GMB Feb 28 '20 at 22:37
  • I advise you to fix the root cause. Use `Transaction` while storing the data – Mojtaba Feb 28 '20 at 22:40
  • @Mojtaba How does a transaction help if the information is never received from the device? – Barmar Feb 28 '20 at 23:09
  • @Barmar, according to what I see, the information is stored partially. This means there is more than one insert/update query while storing the data. So, Transactions could help definitely – Mojtaba Feb 29 '20 at 03:48
  • The question says "Sometimes device lost connection and device is not recording.". The missing data has nothing to do with collisions between multiple writers, it just wasn't read from the transmitter. – Barmar Feb 29 '20 at 17:22

0 Answers0