I am trying to figure out how to group a query result where the final Grouping should happen where the time difference is less than let’s say one minute.
I have watermeter that logs my water usage and I am trying to group the results so that the graphs will make more sense. My sql queries for grouping the water usage per Year, Month, Day and hour are perfect, but then I would like to drill down to where the final result shows me a grouping where as an example I water the grass.
My Table Structure looks like:
id liter total_liters date time dater
9 3 184 2020/12/06 16:14:58 2020/12/06 16:14
10 1 185 2020/12/06 16:15:04 2020/12/06 16:15
11 3 188 2020/12/06 16:26:49 2020/12/06 16:26
12 2 190 2020/12/06 16:26:55 2020/12/06 16:26
13 2 192 2020/12/06 16:27:01 2020/12/06 16:27
14 1 193 2020/12/06 17:32:16 2020/12/06 17:32
15 1 194 2020/12/06 17:32:22 2020/12/06 17:32
16 1 195 2020/12/06 17:32:28 2020/12/06 17:32
17 1 196 2020/12/06 17:32:35 2020/12/06 17:32
18 1 197 2020/12/06 17:32:41 2020/12/06 17:32
19 1 198 2020/12/06 17:32:47 2020/12/06 17:32
20 1 199 2020/12/06 17:32:53 2020/12/06 17:32
21 1 200 2020/12/06 17:32:59 2020/12/06 17:32
22 1 201 2020/12/06 17:35:05 2020/12/06 17:35
23 1 202 2020/12/06 17:35:17 2020/12/06 17:35
24 1 203 2020/12/06 17:35:23 2020/12/06 17:35
25 1 204 2020/12/06 17:35:29 2020/12/06 17:35
26 1 205 2020/12/06 17:35:41 2020/12/06 17:35
27 1 206 2020/12/06 17:43:05 2020/12/06 17:43
28 3 209 2020/12/06 17:43:11 2020/12/06 17:43
29 2 211 2020/12/06 17:43:17 2020/12/06 17:43
30 2 213 2020/12/06 17:43:23 2020/12/06 17:43
31 2 215 2020/12/06 17:43:29 2020/12/06 17:43
32 3 218 2020/12/06 17:43:36 2020/12/06 17:43
33 2 220 2020/12/06 17:43:42 2020/12/06 17:43
And my current query looks like:
SELECT DATE_FORMAT(dater,'%H:%i') AS dater,
YEAR(dater),
MONTHNAME(dater),
DAY(dater),
HOUR(dater),
MINUTE(dater),
SUM(liter) as liter
FROM watermeter
WHERE date LIKE '2020-12-08'
GROUP BY YEAR(date), MONTHNAME(date), DAY(dater), HOUR(dater), MINUTE(dater)
ORDER BY id ASC`
The result should be to sum the Liters together by grouping them by Year then Month then Day then Hour and then it should group the results where the time difference is less than 60 seconds.
I might end up by grouping them Year, Month, Day and then by time difference is less than 60 seconds .
Like
2020-12-06 17:35:05 5 Liters
2020-12-06 17:43:05 13 Liters
Here is a phpmyaddmin sql dump if it helps
-- phpMyAdmin SQL Dump
-- version 4.6.6deb5
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Dec 10, 2020 at 07:27 AM
-- Server version: 10.3.17-MariaDB-0+deb10u1
-- PHP Version: 7.3.11-1~deb10u1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `mysensors`
--
-- --------------------------------------------------------
--
-- Table structure for table `watermeter`
--
CREATE TABLE `watermeter` (
`id` int(10) NOT NULL,
`liter` int(11) NOT NULL,
`total_liters` int(11) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`dater` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `watermeter`
--
INSERT INTO `watermeter` (`id`, `liter`, `total_liters`, `date`, `time`, `dater`) VALUES
(9, 3, 184, '2020-12-06', '16:14:58', '2020-12-06 16:14:58'),
(10, 1, 185, '2020-12-06', '16:15:04', '2020-12-06 16:15:04'),
(11, 3, 188, '2020-12-06', '16:26:49', '2020-12-06 16:26:49'),
(12, 2, 190, '2020-12-06', '16:26:55', '2020-12-06 16:26:55'),
(13, 2, 192, '2020-12-06', '16:27:01', '2020-12-06 16:27:01'),
(14, 1, 193, '2020-12-06', '17:32:16', '2020-12-06 17:32:16'),
(15, 1, 194, '2020-12-06', '17:32:22', '2020-12-06 17:32:22'),
(16, 1, 195, '2020-12-06', '17:32:28', '2020-12-06 17:32:28'),
(17, 1, 196, '2020-12-06', '17:32:35', '2020-12-06 17:32:35'),
(18, 1, 197, '2020-12-06', '17:32:41', '2020-12-06 17:32:41'),
(19, 1, 198, '2020-12-06', '17:32:47', '2020-12-06 17:32:47'),
(20, 1, 199, '2020-12-06', '17:32:53', '2020-12-06 17:32:53'),
(21, 1, 200, '2020-12-06', '17:32:59', '2020-12-06 17:32:59'),
(22, 1, 201, '2020-12-06', '17:35:05', '2020-12-06 17:35:05'),
(23, 1, 202, '2020-12-06', '17:35:17', '2020-12-06 17:35:17'),
(24, 1, 203, '2020-12-06', '17:35:23', '2020-12-06 17:35:23'),
(25, 1, 204, '2020-12-06', '17:35:29', '2020-12-06 17:35:29'),
(26, 1, 205, '2020-12-06', '17:35:41', '2020-12-06 17:35:41'),
(27, 1, 206, '2020-12-06', '17:43:05', '2020-12-06 17:43:05'),
(28, 3, 209, '2020-12-06', '17:43:11', '2020-12-06 17:43:11'),
(29, 2, 211, '2020-12-06', '17:43:17', '2020-12-06 17:43:17'),
(30, 2, 213, '2020-12-06', '17:43:23', '2020-12-06 17:43:23'),
(31, 2, 215, '2020-12-06', '17:43:29', '2020-12-06 17:43:29'),
(32, 3, 218, '2020-12-06', '17:43:36', '2020-12-06 17:43:36'),
(33, 2, 220, '2020-12-06', '17:43:42', '2020-12-06 17:43:42');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `watermeter`
--
ALTER TABLE `watermeter`
ADD PRIMARY KEY (`id`),
ADD KEY `dater` (`dater`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `watermeter`
--
ALTER TABLE `watermeter`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1061;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
UPDATE 1.
Making little progress I think – Totals are not right yet.
SELECT '(a.dater, b.dater)', DATE_FORMAT(a.dater,'%H:%i') AS dater,
YEAR(a.dater),
MONTHNAME(a.dater),
DAY(a.dater),
HOUR(a.dater),
MINUTE(a.dater),
a.time,
SUM(a.liter) as liter
FROM watermeter a
INNER JOIN watermeter b
ON b.dater >= a.dater
WHERE b.dater <= DATE_ADD(a.dater, INTERVAL 60 SECOND)
AND a.date LIKE '2020-12-08' GROUP BY YEAR(a.date), MONTHNAME(a.date), DAY(a.dater), HOUR(a.dater), MINUTE(a.dater)
ORDER BY a.id ASC
Update2
So Update one does not give me the correct result. Tried now the following that I got from : MySQL GROUP BY DateTime +/- 3 seconds but also no joy yet.
SELECT COUNT(liter),DAY(dater),HOUR(dater),MINUTE(dater)
FROM watermeter
JOIN (SELECT watermeter.id, MAX(S.dater) AS ChainStartTime
FROM watermeter
JOIN (SELECT DISTINCT a.dater
FROM watermeter a
LEFT JOIN watermeter b
ON (b.dater >= a.dater - INTERVAL 60 SECOND
AND b.dater < a.dater)
WHERE b.dater IS NULL
AND a.date LIKE '2020-12-06') S
ON watermeter.dater >= S.dater
GROUP BY watermeter.id) GroupingQuery
ON watermeter.id = GroupingQuery.id
GROUP BY GroupingQuery.ChainStartTime
2
Answers
With the help of Luuk's code and learning a lot about "case" and "lag" and lead" and using nested selects etc. I was able to get a working query for what I wanted.
With this I was able to sum all values where the time difference is less than 60 second from without loosing a single line.
First find the difference in time from the previous row:
output:
etc…
Then determine which times you would like to see, because they have a difference to their previous row which is larger than 60 seconds.
(The
id
of the column is show inx
)output:
etc…
Next step is to determine the max(id) which ‘belongs’ to the
x
:output:
Note that
x
andy
are the minimum and maximumid
for your group.Finally (this messy stuff):
gives output:
I do hope this is close to the expected output…