Question 347065 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Query for Spread/Difference of Energy Counter and missing Value interpolation

I have an energy counter (kWh) recorded in a MySQL database every 15 minutes.
Sometimes the recording fails for several reasons (power outage, computer reboot for updates…) and values are missing.

The table looks as followed:

id      Time                Energy
27800   13.02.2024 23:30:01 651720048
27801   13.02.2024 23:45:00 651720672
(missing)
27802   14.02.2024 00:15:02 651721917
27803   14.02.2024 00:30:00 651722540
27804   14.02.2024 00:45:00 651723129
27805   14.02.2024 01:00:02 651723769
27806   14.02.2024 01:15:01 651724405
27807   14.02.2024 01:30:01 651725030
(missing)
27808   14.02.2024 02:00:01 651726275
27809   14.02.2024 02:15:02 651726880
27810   14.02.2024 02:30:01 651727519
27811   14.02.2024 02:45:00 651728130
27812   14.02.2024 03:00:02 651728751
27813   14.02.2024 03:15:02 651729381

I am looking for a SQL query which returns the consumption (spread/difference between energy counter values) in a certain (variable) time span (eg. 15 minutes, 60 minutes, 24 hours, 1 month…) which also considers missing values by interpolation.

The result should look as showed there in the columns Consumption 15m and Consumption 1h:

id      Time                Energy      Consumption 15m     Consumption 1h
27800   13.02.2024 23:30:01 651720048   -   
27801   13.02.2024 23:45:00 651720672   624 
(missing)                   651721294.5 622.5               -
27802   14.02.2024 00:15:02 651721917   622.5   
27803   14.02.2024 00:30:00 651722540   623 
27804   14.02.2024 00:45:00 651723129   589 
27805   14.02.2024 01:00:02 651723769   640                 2474,5
27806   14.02.2024 01:15:01 651724405   636 
27807   14.02.2024 01:30:01 651725030   625 
(missing)                   651725652.5 622.5   
27808   14.02.2024 02:00:01 651726275   622.5               2506
27809   14.02.2024 02:15:02 651726880   605 
27810   14.02.2024 02:30:01 651727519   639 
27811   14.02.2024 02:45:00 651728130   611 
27812   14.02.2024 03:00:02 651728751   621                 2476
27813   14.02.2024 03:15:02 651729381   630

I guess it is somewhow required to find the closest two values of two given time points (e.g. 14.02.2024 00:00:00 and 14.02.2024 01:00:00) and create an interpolated value of the energy counter to build then the difference of it.

Which query could achieve that desired result?

Tags:

2

Answers


  1. 0

    One option is to generate all 15 minutes intervals between your start and end time – JOIN intervals to your table data filling up the missing rows – approximate the missing consumption and populate missing rows…

    WITH      --  S a m p l e    D a t a :
    tbl as
        ( Select 27800 "ID", To_Date('13.02.2024 23:30:01', 'dd.mm.yyyy hh24:mi:ss') "TIME", 651720048 "ENERGY" From Dual Union All
          Select 27801, To_Date('13.02.2024 23:45:00', 'dd.mm.yyyy hh24:mi:ss'), 651720672 From Dual Union All
          --
          Select 27802,  To_Date('14.02.2024 00:15:02', 'dd.mm.yyyy hh24:mi:ss'), 651721917 From Dual Union All
          Select 27803,  To_Date('14.02.2024 00:30:00', 'dd.mm.yyyy hh24:mi:ss'), 651722540 From Dual Union All
          Select 27804,  To_Date('14.02.2024 00:45:00', 'dd.mm.yyyy hh24:mi:ss'), 651723129 From Dual Union All
          Select 27805,  To_Date('14.02.2024 01:00:02', 'dd.mm.yyyy hh24:mi:ss'), 651723769 From Dual Union All
          Select 27806,  To_Date('14.02.2024 01:15:01', 'dd.mm.yyyy hh24:mi:ss'), 651724405 From Dual Union All
          Select 27807,  To_Date('14.02.2024 01:30:01', 'dd.mm.yyyy hh24:mi:ss'), 651725030 From Dual Union All
          --
          Select 27808,  To_Date('14.02.2024 02:00:01', 'dd.mm.yyyy hh24:mi:ss'), 651726275 From Dual Union All
          Select 27809,  To_Date('14.02.2024 02:15:02', 'dd.mm.yyyy hh24:mi:ss'), 651726880 From Dual Union All
          Select 27810,  To_Date('14.02.2024 02:30:01', 'dd.mm.yyyy hh24:mi:ss'), 651727519 From Dual Union All
          Select 27811,  To_Date('14.02.2024 02:45:00', 'dd.mm.yyyy hh24:mi:ss'), 651728130 From Dual Union All
          Select 27812,  To_Date('14.02.2024 03:00:02', 'dd.mm.yyyy hh24:mi:ss'), 651728751 From Dual Union All
          Select 27813,  To_Date('14.02.2024 03:15:02', 'dd.mm.yyyy hh24:mi:ss'), 651729381 From Dual 
        ),

    Create CTE (named it intervals) to generate all 15 minutes intervals…

    --  Generate intervals of 15 minutes between first and last TIME column of your data
  intervals AS
    ( Select    MIN_TIME + ( (LEVEL - 1) * 15 ) / (24*60) "TIME", 
                To_Char(MIN_TIME + ( (LEVEL - 1) * 15 ) / (24*60), 'dd.mm.yyyy hh24:mi:ss')   "DATE_TIME"
      From
        ( Select       To_Date( To_Char(Min(Trunc(t.TIME, 'mi')), 'dd.mm.yyyy hh24:mi'), 'dd.mm.yyyy hh24:mi') "MIN_TIME", 
                       To_Date(To_Char(Max(Trunc(t.TIME, 'mi')), 'dd.mm.yyyy hh24:mi'), 'dd.mm.yyyy hh24:mi') "MAX_TIME", 
                       CEIL( ( ( Max(Trunc(t.TIME, 'mi')) - Min(Trunc(t.TIME, 'mi')) ) *24 *60 ) / 4) "DIFF"
          From         tbl t
        )
      Connect By   LEVEL <= CEIL(DIFF / 4) + 1
    ),

    … join intervals to your table data doing approximations (where needed) an populate missing rows and to prepare data to calculate consumptions for last 15 min / 1 hour

      grid AS
    ( Select      Coalesce(To_Char(t.ID), 'APPROX-' || ROW_NUMBER() OVER(Partition By Case When t.TIME Is Null Then 1 End Order By t.ID)) "ID",
                  Coalesce(To_Char(t.TIME, 'dd.mm.yyyy hh24:mi:ss'), i.DATE_TIME) "TIME", Coalesce(t.TIME, i.TIME) "DATE_TIME", 
                  Coalesce( t.ENERGY, 
                            LAST_VALUE(t.ENERGY) OVER( Order By Coalesce(t.TIME, i.TIME) Rows Between Unbounded Preceding And 1 Preceding ) + 
                             ( LEAD(t.ENERGY) OVER(Order By Coalesce(t.TIME, i.TIME) ) - Coalesce( t.ENERGY, LAG(t.ENERGY) OVER(Order By Coalesce(t.TIME, i.TIME) ) ) ) / 2 
                          ) "ENERGY", 
                  LAST_VALUE(To_Char(Coalesce(t.TIME, i.TIME), 'dd.mm.yyyy hh24')) Over(Order By Coalesce(t.TIME, i.TIME)) "HOUR_ID"
      From        intervals i
      Left Join   tbl t ON( Coalesce(Trunc(t.TIME, 'mi'), i.TIME) = i.TIME )
    )

    --    M a i n    S Q L :
SELECT      ID, TIME, ENERGY, ENERGY - LAG(ENERGY) Over(Order By TIME) "CONSUMPTION_LAST_15_MIN",
            Case When To_Char(DATE_TIME, 'mi') = '00' Then 
                       Max(ENERGY) OVER(Partition By HOUR_ID) - Min(ENERGY) OVER(Partition By HOUR_ID) + (ENERGY - LAG(ENERGY) Over(Order By TIME))
            End "CONSUMPTION_THIS_HOUR"
FROM        grid

    /*     R e s u l t :
ID            TIME                     ENERGY  CONSUMPTION_LAST_15_MIN  CONSUMPTION_THIS_HOUR
------------  -------------------  ----------  -----------------------  ---------------------
27800         13.02.2024 23:30:01   651720048                                              
27801         13.02.2024 23:45:00   651720672                      624                     
APPROX-1      14.02.2024 00:00:00   651721295                    622,5                  2457
27802         14.02.2024 00:15:02   651721917                    622,5                      
27803         14.02.2024 00:30:00   651722540                      623                      
27804         14.02.2024 00:45:00   651723129                      589                      
27805         14.02.2024 01:00:02   651723769                      640                 2523.5
27806         14.02.2024 01:15:01   651724405                      636                      
27807         14.02.2024 01:30:01   651725030                      625                      
APPROX-2      14.02.2024 01:45:00   651725653                    622,5                      
27808         14.02.2024 02:00:01   651726275                    622,5                 2477.5
27809         14.02.2024 02:15:02   651726880                      605                      
27810         14.02.2024 02:30:01   651727519                      639                      
27811         14.02.2024 02:45:00   651728130                      611                      
27812         14.02.2024 03:00:02   651728751                      621                   1251
27813         14.02.2024 03:15:02   651729381                      630                      
*/
    Login or Signup to reply.
  2. 0

    See example.

    Recursive add missing rows with interpolated time and energy.

    with recursive r as( -- anchor part - all existing rows
select id, Time,Energy  
    -- time diff rounded to 15 min intervals count
  ,round(time_to_sec(timediff(lead(time,1,time)over(order by time),time))/900) td15mr
  ,lead(Energy,1,Energy)over(order by time)-Energy eDiff
from test
  union all  -- recursive part  add missing rows only
select id, addtime(Time,'00:15:00'),Energy+(eDiff/td15mr)   
  ,td15mr-1  as td15mr
  ,eDiff-(eDiff/td15mr)
from r where td15mr>1
)
select id, Time,Energy
  ,Energy-lag(Energy,1,Energy)over(order by time) cons15m
  ,case when minute(date_add(time,INTERVAL 1 minute))<15 then
     energy-(min(energy)over(order by time rows between 4 preceding and current row) )
   end cons1h
  ,td15mr,eDiff
from r
order by time;

    For test data

    create table test (id int,Time datetime, Energy double);
insert into test values
 (27800   ,'2024.02.13 23:30:01', 651720048)
,(27801   ,'2024.02.13 23:45:00', 651720672)
-- (missing)
,(27802   ,'2024.02.14 00:15:02', 651721917)
,(27803   ,'2024.02.14 00:30:00', 651722540)
,(27804   ,'2024.02.14 00:45:00', 651723129)
,(27805   ,'2024.02.14 01:00:02', 651723769)
,(27806   ,'2024.02.14 01:15:01', 651724405)
,(27807   ,'2024.02.14 01:30:01', 651725030)
-- (missing)
 ,(27808   ,'2024.02.14 02:00:01', 651726275)
,(27809   ,'2024.02.14 02:15:02', 651726880)
,(27810   ,'2024.02.14 02:30:01', 651727519)
,(27811   ,'2024.02.14 02:45:00', 651728130)
,(27812   ,'2024.02.14 03:00:02', 651728751)
,(27813   ,'2024.02.14 03:15:02', 651729381)
,(27814   ,'2024.02.14 03:29:58', 651729400)
  -- missing 3 rows
,(27815   ,'2024.02.14 04:30:02', 651731800)
;

    Output is

    id Time Energy cons15m cons1h td15mr eDiff
    27800 2024-02-13 23:30:01 651720048 0 null 1 624
    27801 2024-02-13 23:45:00 651720672 624 null 2 1245
    27801 2024-02-14 00:00:00 651721294.5 622.5 1246.5 1 622.5
    27802 2024-02-14 00:15:02 651721917 622.5 null 1 623
    27803 2024-02-14 00:30:00 651722540 623 null 1 589
    27804 2024-02-14 00:45:00 651723129 589 null 1 640
    27805 2024-02-14 01:00:02 651723769 640 2474.5 1 636
    27806 2024-02-14 01:15:01 651724405 636 null 1 625
    27807 2024-02-14 01:30:01 651725030 625 null 2 1245
    27807 2024-02-14 01:45:01 651725652.5 622.5 null 1 622.5
    27808 2024-02-14 02:00:01 651726275 622.5 2506 1 605
    27809 2024-02-14 02:15:02 651726880 605 null 1 639
    27810 2024-02-14 02:30:01 651727519 639 null 1 611
    27811 2024-02-14 02:45:00 651728130 611 null 1 621
    27812 2024-02-14 03:00:02 651728751 621 2476 1 630
    27813 2024-02-14 03:15:02 651729381 630 null 1 19
    27814 2024-02-14 03:29:58 651729400 19 null 4 2400
    27814 2024-02-14 03:44:58 651730000 600 null 3 1800
    27814 2024-02-14 03:59:58 651730600 600 1849 2 1200
    27814 2024-02-14 04:14:58 651731200 600 null 1 600
    27815 2024-02-14 04:30:02 651731800 600 null 0 0

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search