0

I have table in MariaDB like this

INSERT INTO `electric_meters` (`id`, `ts`, `id_sensor`, `value`) VALUES
(166063,    '2024-02-16 07:00:00.625',  'ats01_TotalCounter',   8801.936),
(166043,    '2024-02-16 06:45:01.008',  'ats01_TotalCounter',   8791.536),
(166021,    '2024-02-16 06:30:00.985',  'ats01_TotalCounter',   8781.552),
(166000,    '2024-02-16 06:15:00.800',  'ats01_TotalCounter',   8771.568),
(165979,    '2024-02-16 05:59:58.365',  'ats01_TotalCounter',   8761.168),
(165961,    '2024-02-16 05:45:01.256',  'ats01_TotalCounter',   8752.016),
(165938,    '2024-02-16 05:30:00.088',  'ats01_TotalCounter',   8743.280),
(165916,    '2024-02-16 05:15:02.090',  'ats01_TotalCounter',   8734.336),
(165895,    '2024-02-16 05:00:00.009',  'ats01_TotalCounter',   8726.016),
(165876,    '2024-02-16 04:45:02.005',  'ats01_TotalCounter',   8718.320),
(165853,    '2024-02-16 04:30:01.950',  'ats01_TotalCounter',   8711.456),
(165832,    '2024-02-16 04:15:00.088',  'ats01_TotalCounter',   8703.136),
(165811,    '2024-02-16 04:00:00.025',  'ats01_TotalCounter',   8696.272),
(165790,    '2024-02-16 03:45:00.089',  'ats01_TotalCounter',   8691.280),
(165769,    '2024-02-16 03:30:01.788',  'ats01_TotalCounter',   8685.872),
(165748,    '2024-02-16 03:15:03.069',  'ats01_TotalCounter',   8680.464),
(165727,    '2024-02-16 02:59:59.009',  'ats01_TotalCounter',   8675.264),
(165706,    '2024-02-16 02:45:00.800',  'ats01_TotalCounter',   8669.024),
(165686,    '2024-02-16 02:30:00.700',  'ats01_TotalCounter',   8664.448),
(165664,    '2024-02-16 02:15:03.090',  'ats01_TotalCounter',   8659.456),
(165643,    '2024-02-16 02:00:04.940',  'ats01_TotalCounter',   8654.672),
(165622,    '2024-02-16 01:44:58.700',  'ats01_TotalCounter',   8650.096);

I need to select last record of every hour, which is closest to hour. Problem is, timestamp is sometimes slightly before (like ID 165979 - 05:59:58.365) and sometimes slightly after (like ID 166063 - 07:00:00.625). Of course, I have multiple sensors, this is just an example with one sensor, and I will select by sensor_id and sort by TS. Correct result should include IDs 166063, 165979, 165895, 165811, 165727, 165643. Is it possible to write this query?

Thank you for your help!

Edit according comment: I tried simply round hours and minutes:

SELECT 
  T.*
FROM electric_meters AS T
JOIN (
  SELECT MAX(`ts`) AS `max_time` FROM electric_meters GROUP BY HOUR(`ts`), DAY(`ts`), YEAR(`ts`)
) AS t
ON (T.`ts` = t.`max_time`)

result is

id,ts,id_sensor,value
165622,2024-02-16 01:44:58.700,ats01_TotalCounter,"8650.096"
165727,2024-02-16 02:59:59.009,ats01_TotalCounter,"8675.264"
165790,2024-02-16 03:45:00.089,ats01_TotalCounter,"8691.280"
165876,2024-02-16 04:45:02.005,ats01_TotalCounter,"8718.320"
165979,2024-02-16 05:59:58.365,ats01_TotalCounter,"8761.168"
166043,2024-02-16 06:45:01.008,ats01_TotalCounter,"8791.536"
166063,2024-02-16 07:00:00.625,ats01_TotalCounter,"8801.936"

Problem is, it picks 7:00 correctly, byt incorrectly 6:45

3
  • Please show what you have tried so far. Feb 16 at 11:18
  • 1
    .. timestamp is sometimes slightly before .. and sometimes slightly after .. What is max. allowed deviation? for example, not more than 5 minutes.. Or you want the closest value regardless the deviation?
    – Akina
    Feb 16 at 13:00
  • It depends.. If it is possible and not HW hungry query, I would like to have it as wide as possible, but I can set some limit if it will be easier, like 5 minutes for example. Data comes in 15 minutes interval, but it can change, mainly because of some problems in HW/network ETC... I can't control it...
    – Ajax
    Feb 16 at 13:39

2 Answers 2

1

I found a solution. Maybe is suboptimal, but suits my needs...

SELECT 
    T.* 
FROM electric_meters AS T
JOIN (
    SELECT 
        MAX(ts) as ts_max,
        DATE_FORMAT(DATE_ADD(ts, INTERVAL 55 MINUTE), '%Y-%m-%d %H:00:00') AS ts_rounded
    FROM electric_meters
    GROUP BY ts_rounded
) AS t ON t.ts_max = T.ts

Trick is to round time to nearest hour with some safe interval, like 5 minutes. So, if you add 55 minutes to any time and format it as %H:00:00, you will get uniform time across whole hour, including 5 minutes before and 5 minutes after. Then, just group and join.

Any other suggestions or observations are welcome!

1
  • Your 55m trick takes care of "nearest". Then the please where there is some chance of optimization is in [Groupwise-max]. See that tag and Groupwise-Max
    – Rick James
    Feb 16 at 18:50
1

If window functions are supported in your version, you could try

select
    `id`, `ts`, `id_sensor`, `value`
from (
    select
        `id`, `ts`, `id_sensor`, `value`,
        row_number() over (
            partition by date_format(date_add(`ts`, interval 30 minute), '%Y-%m-%d %H:00:00')
            order by abs(timestampdiff(microsecond, `ts`, date_format(date_add(`ts`, interval 30 minute), '%Y-%m-%d %H:00:00'))), `ts` desc
        ) as `row_num`
    from
        `electric_meters`
) as `t`
where `t`.`row_num` = 1;

Another option could be to add the following generated columns

alter table `electric_meters` add column `closest_hour` int unsigned as (floor((unix_timestamp(`ts`) + 1800) / 3600) * 3600) stored;
alter table `electric_meters` add column `diff` int unsigned as (abs(unix_timestamp(`ts`) - `closest_hour`) * 1000) stored;

The query would be

select
    `t1`.`id`, `t1`.`ts`, `t1`.`id_sensor`, `t1`.`value`
from
    `electric_meters` as `t1`
    left join `electric_meters` as `t2`
        on `t1`.`closest_hour` = `t2`.`closest_hour`
        and `t1`.`diff` > `t2`.`diff`
where
    `t2`.`diff` is null;

The following composite index is needed for this option

alter table `electric_meters` add index `idx_closest_hour_diff` (`closest_hour`, `diff`);

or may be a covering one instead depending on which attributes are selected

alter table `electric_meters` add index `idx_closest_hour_diff_ts_id_sensor_value` (`closest_hour`, `diff`, `ts`, `id_sensor`, `value`);
3
  • This selection fail, when TS is slightly below hour, like 05:59:58. It picked 07:00:00, 06:45:01, 05:59:58,...
    – Ajax
    Feb 16 at 13:35
  • @Ajax I've modified the query. Can you take a look? Feb 17 at 9:10
  • It actually works! Will test it more, but looks like slightly slower to execute.
    – Ajax
    Mar 2 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.