MySql query to check time value is continous based on from and to Date

I have a table as follow:

timestamp || GridPowerTotal
22-02-2017 13:00 || 13405
22-02-2017 13:10 || 13608
22-02-2017 13:20 || 1678
22-02-2017 13:30 || 23456
23-02-2017 13:40 || 0
23-02-2017 13:50 || 12345
23-02-2017 14:00 || 23456
23-02-2017 14:10 || 0
23-02-2017 14:20 || 23
23-02-2017 14:30 || 23467
24-02-2017 14:40 || 7654
24-02-2017 14:50 || 7655
24-02-2017 15:00 || 8906
24-02-2017 15:10 || 4567

In the above Table we can see that Time is recorded every 10min…But in some situation time will be not recorded…What is the sql query to check timestamp value exists every 10min and later calculate sum

Below is sql query that ive used to retrieve sum based on daywise

SELECT DATE(timestamp) as DATE, SUM(`gridpowertotal`) as totalCount FROM inverterlog where inverter = "1" AND Date(timestamp) BETWEEN "2017-02-22" AND "2017-02-24"  GROUP BY DATE(timestamp) 

Below is output

| 2017-02-22 |     613661 |
| 2017-02-23 |     605278 |
| 2017-02-24 |     627719 |

above query is returning sum ofl days present in my database…But I need to check time interval is continous every 10 min and then calculate the sum based on user start time and end date.

Below is my html code

 <input id="dateIpone" name="dateone" type="date" style="display:none;" >
 <input id="dateIptwo" name="datetwo" type="date" style="display:none;" >

Below is my php code

  $fromdate = $_SESSION['start'];
  $todate = $_SESSION['end'];
  $inverter = $_SESSION['inverter'];
  $query = "SELECT  DATE(timestamp) as DATE, SUM(`gridpowertotal`) totalCount FROM inverterlog   GROUPBY DATE(timestamp)";	   

how to check time continuity based on $fromdate and $todate input in the above query

those are related but different requirements

the SUM actually doesn’t depend on missing entries, does it

do you need to see which entries are missing (a more difficult query) or do you simply want to know whether any are missing, or how many are missing

and please be careful when you use the terms date, time, and datetime – they are three different concepts

1 Like

@r937…sorry for replying late…
Now on the topic …i want to know whether any entries are missing if yes how many are missing…so that i can notify the user…while calculating…

okay, take your $fromdatetime and $todatetime and calculate, using arithmetic, how many time intervals there should be between them

then do a COUNT(*) of the rows in the table between those two datetimes

subtract and there you have it, the number of missing entries

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.