Selecting rows which more than one second different from each other MYSQL
0
This is a view from my table where i only selected the DateTime and id.
What i am looking for i a query that only shows records which aren't within an ID range of 5 of each other. Or which are more than a second different from each other.
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 14:20:56 | 67228 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:07:13 | 68086 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-04 17:51:12 | 68199 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-05 05:23:00 | 68519 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
so my table would look like this:
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
It doesn't matter if 2018-06-04 14:20:55 or if 2018-06-04 14:20:56 gets shown in the query. Same goes for the rest, as long as it's one of them
I've tried this query but it only works if the ID's are contiginous
SELECT t0.*, t1.id
FROM table t0
LEFT JOIN table t1
ON t0.id + 1 = t1.id
WHERE t1.id IS NULL;
mysql sql datetime
edited Nov 20 '18 at 10:21
Salman A
177k66338427
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
add a comment |
0
This is a view from my table where i only selected the DateTime and id.
What i am looking for i a query that only shows records which aren't within an ID range of 5 of each other. Or which are more than a second different from each other.
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 14:20:56 | 67228 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:07:13 | 68086 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-04 17:51:12 | 68199 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-05 05:23:00 | 68519 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
so my table would look like this:
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
It doesn't matter if 2018-06-04 14:20:55 or if 2018-06-04 14:20:56 gets shown in the query. Same goes for the rest, as long as it's one of them
I've tried this query but it only works if the ID's are contiginous
SELECT t0.*, t1.id
FROM table t0
LEFT JOIN table t1
ON t0.id + 1 = t1.id
WHERE t1.id IS NULL;
mysql sql datetime
edited Nov 20 '18 at 10:21
Salman A
177k66338427
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
1
"It doesn't matter which row is shown, as long as at least of them is not shown." This sentence makes no sense to me.
– dmikester1
Nov 15 '18 at 14:46
I think I've made it more clear with an example
– Martijn van Amsterdam
Nov 15 '18 at 14:51
What if you have 1-4-7? Do you want 1 and 7? Or only 1?
– Gordon Linoff
Nov 15 '18 at 14:54
I don't understand your question, but look at it this way: assume that when there is only 1 second different they are duplicate and i want to remove the duplicates and it doesn't matter which one gets removed.
– Martijn van Amsterdam
Nov 15 '18 at 14:56
add a comment |
0
0
0
This is a view from my table where i only selected the DateTime and id.
What i am looking for i a query that only shows records which aren't within an ID range of 5 of each other. Or which are more than a second different from each other.
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 14:20:56 | 67228 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:07:13 | 68086 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-04 17:51:12 | 68199 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-05 05:23:00 | 68519 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
so my table would look like this:
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
It doesn't matter if 2018-06-04 14:20:55 or if 2018-06-04 14:20:56 gets shown in the query. Same goes for the rest, as long as it's one of them
I've tried this query but it only works if the ID's are contiginous
SELECT t0.*, t1.id
FROM table t0
LEFT JOIN table t1
ON t0.id + 1 = t1.id
WHERE t1.id IS NULL;
mysql sql datetime
edited Nov 20 '18 at 10:21
Salman A
177k66338427
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
This is a view from my table where i only selected the DateTime and id.
What i am looking for i a query that only shows records which aren't within an ID range of 5 of each other. Or which are more than a second different from each other.
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 14:20:56 | 67228 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:07:13 | 68086 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-04 17:51:12 | 68199 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-05 05:23:00 | 68519 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
so my table would look like this:
---------------------+--------+
| DateTime | id |
+---------------------+--------+
| 2018-06-02 16:10:13 | 61863 |
| 2018-06-03 14:04:13 | 63715 |
| 2018-06-03 17:34:28 | 64339 |
| 2018-06-04 14:20:55 | 67227 |
| 2018-06-04 15:39:47 | 67845 |
| 2018-06-04 17:07:12 | 68085 |
| 2018-06-04 17:51:11 | 68197 |
| 2018-06-05 05:22:59 | 68518 |
| 2018-06-07 10:28:28 | 74568 |
| 2018-06-07 16:18:36 | 76386 |
It doesn't matter if 2018-06-04 14:20:55 or if 2018-06-04 14:20:56 gets shown in the query. Same goes for the rest, as long as it's one of them
I've tried this query but it only works if the ID's are contiginous
SELECT t0.*, t1.id
FROM table t0
LEFT JOIN table t1
ON t0.id + 1 = t1.id
WHERE t1.id IS NULL;
mysql sql datetime
mysql sql datetime
edited Nov 20 '18 at 10:21
Salman A
177k66338427
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
edited Nov 20 '18 at 10:21
Salman A
177k66338427
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
edited Nov 20 '18 at 10:21
Salman A
177k66338427
edited Nov 20 '18 at 10:21
Salman A
177k66338427
edited Nov 20 '18 at 10:21
Salman A
177k66338427
177k66338427
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
asked Nov 15 '18 at 14:45
Martijn van AmsterdamMartijn van Amsterdam
12710
12710
1
"It doesn't matter which row is shown, as long as at least of them is not shown." This sentence makes no sense to me.
– dmikester1
Nov 15 '18 at 14:46
I think I've made it more clear with an example
– Martijn van Amsterdam
Nov 15 '18 at 14:51
What if you have 1-4-7? Do you want 1 and 7? Or only 1?
– Gordon Linoff
Nov 15 '18 at 14:54
I don't understand your question, but look at it this way: assume that when there is only 1 second different they are duplicate and i want to remove the duplicates and it doesn't matter which one gets removed.
– Martijn van Amsterdam
Nov 15 '18 at 14:56
add a comment |
1
"It doesn't matter which row is shown, as long as at least of them is not shown." This sentence makes no sense to me.
– dmikester1
Nov 15 '18 at 14:46
I think I've made it more clear with an example
– Martijn van Amsterdam
Nov 15 '18 at 14:51
What if you have 1-4-7? Do you want 1 and 7? Or only 1?
– Gordon Linoff
Nov 15 '18 at 14:54
I don't understand your question, but look at it this way: assume that when there is only 1 second different they are duplicate and i want to remove the duplicates and it doesn't matter which one gets removed.
– Martijn van Amsterdam
Nov 15 '18 at 14:56
1
1
"It doesn't matter which row is shown, as long as at least of them is not shown." This sentence makes no sense to me.
– dmikester1
Nov 15 '18 at 14:46
"It doesn't matter which row is shown, as long as at least of them is not shown." This sentence makes no sense to me.
– dmikester1
Nov 15 '18 at 14:46
I think I've made it more clear with an example
– Martijn van Amsterdam
Nov 15 '18 at 14:51
I think I've made it more clear with an example
– Martijn van Amsterdam
Nov 15 '18 at 14:51
What if you have 1-4-7? Do you want 1 and 7? Or only 1?
– Gordon Linoff
Nov 15 '18 at 14:54
What if you have 1-4-7? Do you want 1 and 7? Or only 1?
– Gordon Linoff
Nov 15 '18 at 14:54
I don't understand your question, but look at it this way: assume that when there is only 1 second different they are duplicate and i want to remove the duplicates and it doesn't matter which one gets removed.
– Martijn van Amsterdam
Nov 15 '18 at 14:56
I don't understand your question, but look at it this way: assume that when there is only 1 second different they are duplicate and i want to remove the duplicates and it doesn't matter which one gets removed.
– Martijn van Amsterdam
Nov 15 '18 at 14:56
add a comment |
2 Answers
2
active
oldest
votes
0
You can use NOT EXISTS like so:
SELECT *
FROM yourdata t
WHERE NOT EXISTS (
SELECT 1
FROM yourdata x
WHERE (x.DateTime > t.DateTime AND x.DateTime <= t.DateTime + INTERVAL 1 SECOND)
OR (x.id > t.id AND x.id <= t.id + 4)
)
Rows with same datetime or id are NOT counted.
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
add a comment |
0
select t1.Datetime, t1.id
from mytable t1
join mytable t2
on t2.Datetime=(select Datetime from mytable t2
where t2.Datetime > t1.Datetime
and t2.id-t1.id > 5
limit 1)
where timestampdiff(second,t1.Datetime,t2.Datetime)>1;
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321976%2fselecting-rows-which-more-than-one-second-different-from-each-other-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can use NOT EXISTS like so:
SELECT *
FROM yourdata t
WHERE NOT EXISTS (
SELECT 1
FROM yourdata x
WHERE (x.DateTime > t.DateTime AND x.DateTime <= t.DateTime + INTERVAL 1 SECOND)
OR (x.id > t.id AND x.id <= t.id + 4)
)
Rows with same datetime or id are NOT counted.
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
add a comment |
0
You can use NOT EXISTS like so:
SELECT *
FROM yourdata t
WHERE NOT EXISTS (
SELECT 1
FROM yourdata x
WHERE (x.DateTime > t.DateTime AND x.DateTime <= t.DateTime + INTERVAL 1 SECOND)
OR (x.id > t.id AND x.id <= t.id + 4)
)
Rows with same datetime or id are NOT counted.
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
add a comment |
0
0
0
You can use NOT EXISTS like so:
SELECT *
FROM yourdata t
WHERE NOT EXISTS (
SELECT 1
FROM yourdata x
WHERE (x.DateTime > t.DateTime AND x.DateTime <= t.DateTime + INTERVAL 1 SECOND)
OR (x.id > t.id AND x.id <= t.id + 4)
)
Rows with same datetime or id are NOT counted.
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
You can use NOT EXISTS like so:
SELECT *
FROM yourdata t
WHERE NOT EXISTS (
SELECT 1
FROM yourdata x
WHERE (x.DateTime > t.DateTime AND x.DateTime <= t.DateTime + INTERVAL 1 SECOND)
OR (x.id > t.id AND x.id <= t.id + 4)
)
Rows with same datetime or id are NOT counted.
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
edited Nov 15 '18 at 15:34
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
answered Nov 15 '18 at 15:15
Salman ASalman A
177k66338427
177k66338427
add a comment |
add a comment |
0
select t1.Datetime, t1.id
from mytable t1
join mytable t2
on t2.Datetime=(select Datetime from mytable t2
where t2.Datetime > t1.Datetime
and t2.id-t1.id > 5
limit 1)
where timestampdiff(second,t1.Datetime,t2.Datetime)>1;
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
add a comment |
0
select t1.Datetime, t1.id
from mytable t1
join mytable t2
on t2.Datetime=(select Datetime from mytable t2
where t2.Datetime > t1.Datetime
and t2.id-t1.id > 5
limit 1)
where timestampdiff(second,t1.Datetime,t2.Datetime)>1;
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
add a comment |
0
0
0
select t1.Datetime, t1.id
from mytable t1
join mytable t2
on t2.Datetime=(select Datetime from mytable t2
where t2.Datetime > t1.Datetime
and t2.id-t1.id > 5
limit 1)
where timestampdiff(second,t1.Datetime,t2.Datetime)>1;
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
select t1.Datetime, t1.id
from mytable t1
join mytable t2
on t2.Datetime=(select Datetime from mytable t2
where t2.Datetime > t1.Datetime
and t2.id-t1.id > 5
limit 1)
where timestampdiff(second,t1.Datetime,t2.Datetime)>1;
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
edited Nov 15 '18 at 15:14
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
answered Nov 15 '18 at 15:03
Michael O.Michael O.
2,8682522
2,8682522
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
add a comment |
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
Yes! Thanks you so much, i've been trying multiple variations on mine, also with the DateTime > INTERVAL 1 second and stuff. But this one is great.
– Martijn van Amsterdam
Nov 15 '18 at 15:08
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321976%2fselecting-rows-which-more-than-one-second-different-from-each-other-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
"It doesn't matter which row is shown, as long as at least of them is not shown." This sentence makes no sense to me.
– dmikester1
Nov 15 '18 at 14:46
I think I've made it more clear with an example
– Martijn van Amsterdam
Nov 15 '18 at 14:51
What if you have 1-4-7? Do you want 1 and 7? Or only 1?
– Gordon Linoff
Nov 15 '18 at 14:54
I don't understand your question, but look at it this way: assume that when there is only 1 second different they are duplicate and i want to remove the duplicates and it doesn't matter which one gets removed.
– Martijn van Amsterdam
Nov 15 '18 at 14:56