0

Im running an Openhab2 instance and collect data aswell as timestamps for important changes in datacollection. Inside my table item45 I store 2 colums Time and Value witch looks like this.

TABLE (item45) Design Screenshot 1 Screenshot 2

Time (datetime,primary) Value (datetime)
...                     ...
2018-10-17 03:08:30     2018-10-17 03:08:30
2018-10-19 00:13:13     2018-10-19 00:13:13
2018-10-19 00:27:58     2018-10-19 00:27:57

Its kind of the design of how Openhab stores data so nothing i can do about that. i now try to use these values in Grafana just as ones like this:

Example of sucessful use

My Problem lies within Grafana im using to plot the querys. It disconnects the graphs if the next older point is outside the viewport. I tried to cope with that with virutally filling up the gaps in data inside the query. Like this:

Currently used Query

SELECT 
1 as value,
'Net Reset' as metric,
UNIX_TIMESTAMP(v.gen_date) AS time_sec
from 
  (select DATE_SUB( FROM_UNIXTIME(1539707286), INTERVAL t3*1000 + t2*100 + t1*10 + t0  HOUR) gen_date from
              (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0
   CROSS JOIN (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
   CROSS JOIN (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2
   CROSS JOIN (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
   ) v
LEFT JOIN item45
    ON DATE_FORMAT( Time, '%Y-%m-%dT%H:00:00') = v.gen_date
where $__timeFilter(v.gen_date)
ORDER BY v.gen_date DESC;

AS u can see im using one of the given "hacks" here to generate hourly fillingdata just set to ones.

I need help to implement this query:

Query to replace FROM_UNIXTIME(...)

SELECT * FROM item45 ORDER BY Time DESC LIMIT 1

into the query 1 where FROM_UNIXTIME(1539707286) to set the actual stop of filling up the future data.

Im running MySQL57-server and currently cant upgrade to version 8 to use the with clause.

Desired Output

Time                    Value
2018-10-18 21:00:00     1 <- Inserted
2018-10-18 22:00:00     1 <- Inserted
2018-10-18 23:00:00     1 <- Inserted
2018-10-19 00:00:00     1 <- Inserted
2018-10-19 00:13:13     1
2018-10-19 00:27:58     1
jan b
  • 29
  • 7
  • It is not clear what you are asking. Provide raw dataset, table schema, your query, your result of that query and expected result you want to get please – Alex Oct 18 '18 at 23:36
  • https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Alex Oct 18 '18 at 23:37
  • Does this help? I just want to replace the constant From_Unixtime() with the second query. – jan b Oct 18 '18 at 23:45
  • No it does not help. Provide raw data. I think that is something on your screenshots, but you should provide it as a text. And then - provide query you are doing/trying to execute now, even if it doesn't work but at least returns 2 columns you want to get. Then provide what that query is returning right now. And only after that - provide your desired output. Keep in mind that all values in raw data set, in current output and desired output should "match" to each other. If some data does not exist in raw dataset - I'll be very surprising to see it in current or desired output – Alex Oct 19 '18 at 12:32
  • I literally dont get what u are asking for im sorry. The given data in table 1 is copied one by one directly out of PHPMyAdmin and is indeed the exact form its presented inside the table. I also gave the query im currently using and the desired output represents the data im getting but is only filled up till the fixed From unixtime constant. I want to swap this out with the second query without a with clause. Is this possible? – jan b Oct 19 '18 at 15:57
  • I mean the output of your query has 3 columns, but your desired output has 2. Make your choice and explain what is wrong with your query result http://sqlfiddle.com/#!9/d8cb3d/2 – Alex Oct 19 '18 at 17:19

0 Answers0