0

I have such this table as below and I trying to calculate the elapsed time for which the value "relay" is to 1 per day by creating a view.

id, date, time, dateandtime,timestamp,sensor,temperature,humidity,status
xx   15/03/2018 11:39:00    15/03/2018 11:39:00.0   15/03/2018 11:39    sensor1 23  41  0
xx   15/03/2018 11:40:00    15/03/2018 11:40:00.0   15/03/2018 11:39    sensor2 23  41  1

Here is the query that I come with, however the output is not as except for the past or previous day...

select date,time,TIMESTAMPDIFF(MINUTE,MAX(Date), timestamp) as minutes FROM temperaturedata where relay = 1 group by date

Here is the results:

date time minutes
2018-03-15 11:39:00 699 
2018-03-16 11:01:00 661

Some clarifications, I am trying to get how long per day and hour the status has been set to 1.

something like:

date, starttime, endtime, minutes;

As starttime would be when the first of instance to 1 appears and endtime the last time, minutes the total amount of minutes for which the status was to 1.

hope it make a bit more sense. :)

Any guidance would be more than welcome.

THanks. J.

Joe
  • 53
  • 6
  • What outcome do you get, and what did you expect? – KIKO Software Mar 15 '18 at 11:59
  • Hi, I have updated the initial post. – Joe Mar 16 '18 at 11:18
  • Since you 'group by date' applying the `MAX()` function to 'date' makes no sense. You probably want to apply it to 'timestamp'. You don't seem to use 'dateandtime'? But this is how far I can go, because I don't understand what you want to achieve. Your question is incomplete. – KIKO Software Mar 16 '18 at 12:13
  • Thank you for your response, this is not really understand able :( I am trying to view how long per day and maybe also per hour le status was to 1. – Joe Mar 16 '18 at 12:56

0 Answers0