Mixing
How to Find Time Difference for state change MySQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
My data is as below.
id device state timestamp
250708 1004-3-007294 9 02/01/2019 9:20
250694 1004-3-007294 8 02/01/2019 9:31
250769 1004-3-007294 7 02/01/2019 10:04
250774 1004-3-007294 5 02/01/2019 10:13
250778 1004-3-007294 4 02/01/2019 10:20
250785 1004-3-007294 11 02/01/2019 10:27
250808 1004-3-007294 4 02/01/2019 10:29
250814 1004-3-007294 11 02/01/2019 10:36
250795 3091-5-007208 7 02/01/2019 10:39
250819 3091-5-007208 5 02/01/2019 10:42
250832 3091-5-007208 4 02/01/2019 10:58
250837 3091-5-007208 11 02/01/2019 11:02
250846 3091-5-007208 4 02/01/2019 11:13
I need to find time difference for all devices when 1st time device state changed from 5 to 4 as in my database device never will go again to state 5, but it will be changing state from 4 to 11, 11 to 4. Please help me to write query.
mysql sql database relational-database
edited Jan 2 at 11:48
Strawberry
26.8k83250
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
add a comment |
1
My data is as below.
id device state timestamp
250708 1004-3-007294 9 02/01/2019 9:20
250694 1004-3-007294 8 02/01/2019 9:31
250769 1004-3-007294 7 02/01/2019 10:04
250774 1004-3-007294 5 02/01/2019 10:13
250778 1004-3-007294 4 02/01/2019 10:20
250785 1004-3-007294 11 02/01/2019 10:27
250808 1004-3-007294 4 02/01/2019 10:29
250814 1004-3-007294 11 02/01/2019 10:36
250795 3091-5-007208 7 02/01/2019 10:39
250819 3091-5-007208 5 02/01/2019 10:42
250832 3091-5-007208 4 02/01/2019 10:58
250837 3091-5-007208 11 02/01/2019 11:02
250846 3091-5-007208 4 02/01/2019 11:13
I need to find time difference for all devices when 1st time device state changed from 5 to 4 as in my database device never will go again to state 5, but it will be changing state from 4 to 11, 11 to 4. Please help me to write query.
mysql sql database relational-database
edited Jan 2 at 11:48
Strawberry
26.8k83250
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
1. Store dates as dates. 2. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 11:49
Incidentally, it seems a little odd (although not impossible) that ids aren't sequential with timetamps.
– Strawberry
Jan 2 at 11:53
add a comment |
1
1
1
1
My data is as below.
id device state timestamp
250708 1004-3-007294 9 02/01/2019 9:20
250694 1004-3-007294 8 02/01/2019 9:31
250769 1004-3-007294 7 02/01/2019 10:04
250774 1004-3-007294 5 02/01/2019 10:13
250778 1004-3-007294 4 02/01/2019 10:20
250785 1004-3-007294 11 02/01/2019 10:27
250808 1004-3-007294 4 02/01/2019 10:29
250814 1004-3-007294 11 02/01/2019 10:36
250795 3091-5-007208 7 02/01/2019 10:39
250819 3091-5-007208 5 02/01/2019 10:42
250832 3091-5-007208 4 02/01/2019 10:58
250837 3091-5-007208 11 02/01/2019 11:02
250846 3091-5-007208 4 02/01/2019 11:13
I need to find time difference for all devices when 1st time device state changed from 5 to 4 as in my database device never will go again to state 5, but it will be changing state from 4 to 11, 11 to 4. Please help me to write query.
mysql sql database relational-database
edited Jan 2 at 11:48
Strawberry
26.8k83250
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
My data is as below.
id device state timestamp
250708 1004-3-007294 9 02/01/2019 9:20
250694 1004-3-007294 8 02/01/2019 9:31
250769 1004-3-007294 7 02/01/2019 10:04
250774 1004-3-007294 5 02/01/2019 10:13
250778 1004-3-007294 4 02/01/2019 10:20
250785 1004-3-007294 11 02/01/2019 10:27
250808 1004-3-007294 4 02/01/2019 10:29
250814 1004-3-007294 11 02/01/2019 10:36
250795 3091-5-007208 7 02/01/2019 10:39
250819 3091-5-007208 5 02/01/2019 10:42
250832 3091-5-007208 4 02/01/2019 10:58
250837 3091-5-007208 11 02/01/2019 11:02
250846 3091-5-007208 4 02/01/2019 11:13
I need to find time difference for all devices when 1st time device state changed from 5 to 4 as in my database device never will go again to state 5, but it will be changing state from 4 to 11, 11 to 4. Please help me to write query.
mysql sql database relational-database
mysql sql database relational-database
edited Jan 2 at 11:48
Strawberry
26.8k83250
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
edited Jan 2 at 11:48
Strawberry
26.8k83250
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
edited Jan 2 at 11:48
Strawberry
26.8k83250
edited Jan 2 at 11:48
Strawberry
26.8k83250
edited Jan 2 at 11:48
Strawberry
26.8k83250
26.8k83250
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
asked Jan 2 at 11:46
bilal shakirbilal shakir
105
105
1. Store dates as dates. 2. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 11:49
Incidentally, it seems a little odd (although not impossible) that ids aren't sequential with timetamps.
– Strawberry
Jan 2 at 11:53
add a comment |
1. Store dates as dates. 2. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 11:49
Incidentally, it seems a little odd (although not impossible) that ids aren't sequential with timetamps.
– Strawberry
Jan 2 at 11:53
1. Store dates as dates. 2. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 11:49
1. Store dates as dates. 2. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 11:49
Incidentally, it seems a little odd (although not impossible) that ids aren't sequential with timetamps.
– Strawberry
Jan 2 at 11:53
Incidentally, it seems a little odd (although not impossible) that ids aren't sequential with timetamps.
– Strawberry
Jan 2 at 11:53
add a comment |
4 Answers
4
active
oldest
votes
0
SELECT A.Device, TIMESTAMPDIFF(SECOND,B.t2,A.t1) as differenceintime
FROM
( SELECT device, MIN(timestamp) as t1
FROM tableName
WHERE state = 5
GROUP BY device
) as A
INNER JOIN
( SELECT device, MIN(timestamp) as t2
FROM tableName
WHERE state = 4
GROUP BY device
) as B
ON A.Device = B.Device
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
add a comment |
0
I think you can use conditional aggregation:
select device,
timestampdiff(second,
min(case when status = 4 then timestamp end),
max(case when status = 5 then timestamp end)
) as diff_in_seconds
from t
group by device;
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
add a comment |
0
If you are using a later version of MySQL you can use the LEAD function to get the next state for each row
SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t;
Which for one device will give:
id device state timestamp NextState NextTimeStamp
---------------------------------------------------------------------------------------------
250795 3091-5-007208 7 2019-02-01 10:39:00 5 2019-02-01 10:42:00
250819 3091-5-007208 5 2019-02-01 10:42:00 4 2019-02-01 10:58:00 <---
250832 3091-5-007208 4 2019-02-01 10:58:00 11 2019-02-01 11:02:00
250837 3091-5-007208 11 2019-02-01 11:02:00 4 2019-02-01 11:13:00
250846 3091-5-007208 4 2019-02-01 11:13:00 NULL NULL
This will allow you to identify when the state has changed from 5 to 4. You just need to put the above query into a subquery, and apply a where clause:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t
) AS t
WHERE t.State = 5
AND t.NextState = 4;
If you are using an older version that does not support the use of LEAD, then you will need to use co-related subqueries to replicate the functionality:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
( SELECT TimeStamp
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) AS NextTimeStamp
FROM t
WHERE t.State = 5
AND ( SELECT State
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) = 4 -- next state = 4 to satisfy changing from 5 to 4
) AS t;
Examples on DB<>Fiddle
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
add a comment |
0
You can use JOIN for this. The following assumes that there is exactly one 5->4 transition so does not bother checking if it was the first transition:
SELECT t1.id, t2.id, TIMESTAMPDIFF(SECOND, t1.timestamp,t2.timestamp) AS diff
FROM t AS t1
INNER JOIN t AS t2 ON t2.device = t1.device AND t2.state = 4 AND t2.timestamp > t1.timestamp
LEFT JOIN t AS tx ON t1.device = tx.device AND tx.timestamp > t1.timestamp AND tx.timestamp < t2.timestamp
WHERE t1.state = 5 AND tx.id IS NULL
The LEFT join in the above example ensures that only consecutive transition is matched (e.g. 250774 is joined to 250778 and 250808 but the latter is discarded).
Demo on db<>fiddle
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
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%2f54005768%2fhow-to-find-time-difference-for-state-change-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
SELECT A.Device, TIMESTAMPDIFF(SECOND,B.t2,A.t1) as differenceintime
FROM
( SELECT device, MIN(timestamp) as t1
FROM tableName
WHERE state = 5
GROUP BY device
) as A
INNER JOIN
( SELECT device, MIN(timestamp) as t2
FROM tableName
WHERE state = 4
GROUP BY device
) as B
ON A.Device = B.Device
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
add a comment |
0
SELECT A.Device, TIMESTAMPDIFF(SECOND,B.t2,A.t1) as differenceintime
FROM
( SELECT device, MIN(timestamp) as t1
FROM tableName
WHERE state = 5
GROUP BY device
) as A
INNER JOIN
( SELECT device, MIN(timestamp) as t2
FROM tableName
WHERE state = 4
GROUP BY device
) as B
ON A.Device = B.Device
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
add a comment |
0
0
0
SELECT A.Device, TIMESTAMPDIFF(SECOND,B.t2,A.t1) as differenceintime
FROM
( SELECT device, MIN(timestamp) as t1
FROM tableName
WHERE state = 5
GROUP BY device
) as A
INNER JOIN
( SELECT device, MIN(timestamp) as t2
FROM tableName
WHERE state = 4
GROUP BY device
) as B
ON A.Device = B.Device
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
SELECT A.Device, TIMESTAMPDIFF(SECOND,B.t2,A.t1) as differenceintime
FROM
( SELECT device, MIN(timestamp) as t1
FROM tableName
WHERE state = 5
GROUP BY device
) as A
INNER JOIN
( SELECT device, MIN(timestamp) as t2
FROM tableName
WHERE state = 4
GROUP BY device
) as B
ON A.Device = B.Device
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
edited Jan 3 at 5:57
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
answered Jan 2 at 11:55
Mudassir HasanMudassir Hasan
20.7k1373112
20.7k1373112
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
add a comment |
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
I am facing error for the declared alias A and B. If I use separate query then its being executed. > 1064 - You have an error in your SQL syntax;
– bilal shakir
Jan 3 at 5:26
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
checknow, i had forgotten FROM clause, corrected the answer
– Mudassir Hasan
Jan 3 at 5:58
add a comment |
0
I think you can use conditional aggregation:
select device,
timestampdiff(second,
min(case when status = 4 then timestamp end),
max(case when status = 5 then timestamp end)
) as diff_in_seconds
from t
group by device;
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
add a comment |
0
I think you can use conditional aggregation:
select device,
timestampdiff(second,
min(case when status = 4 then timestamp end),
max(case when status = 5 then timestamp end)
) as diff_in_seconds
from t
group by device;
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
add a comment |
0
0
0
I think you can use conditional aggregation:
select device,
timestampdiff(second,
min(case when status = 4 then timestamp end),
max(case when status = 5 then timestamp end)
) as diff_in_seconds
from t
group by device;
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
I think you can use conditional aggregation:
select device,
timestampdiff(second,
min(case when status = 4 then timestamp end),
max(case when status = 5 then timestamp end)
) as diff_in_seconds
from t
group by device;
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
answered Jan 2 at 11:56
Gordon LinoffGordon Linoff
794k37318422
794k37318422
add a comment |
add a comment |
0
If you are using a later version of MySQL you can use the LEAD function to get the next state for each row
SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t;
Which for one device will give:
id device state timestamp NextState NextTimeStamp
---------------------------------------------------------------------------------------------
250795 3091-5-007208 7 2019-02-01 10:39:00 5 2019-02-01 10:42:00
250819 3091-5-007208 5 2019-02-01 10:42:00 4 2019-02-01 10:58:00 <---
250832 3091-5-007208 4 2019-02-01 10:58:00 11 2019-02-01 11:02:00
250837 3091-5-007208 11 2019-02-01 11:02:00 4 2019-02-01 11:13:00
250846 3091-5-007208 4 2019-02-01 11:13:00 NULL NULL
This will allow you to identify when the state has changed from 5 to 4. You just need to put the above query into a subquery, and apply a where clause:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t
) AS t
WHERE t.State = 5
AND t.NextState = 4;
If you are using an older version that does not support the use of LEAD, then you will need to use co-related subqueries to replicate the functionality:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
( SELECT TimeStamp
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) AS NextTimeStamp
FROM t
WHERE t.State = 5
AND ( SELECT State
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) = 4 -- next state = 4 to satisfy changing from 5 to 4
) AS t;
Examples on DB<>Fiddle
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
add a comment |
0
If you are using a later version of MySQL you can use the LEAD function to get the next state for each row
SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t;
Which for one device will give:
id device state timestamp NextState NextTimeStamp
---------------------------------------------------------------------------------------------
250795 3091-5-007208 7 2019-02-01 10:39:00 5 2019-02-01 10:42:00
250819 3091-5-007208 5 2019-02-01 10:42:00 4 2019-02-01 10:58:00 <---
250832 3091-5-007208 4 2019-02-01 10:58:00 11 2019-02-01 11:02:00
250837 3091-5-007208 11 2019-02-01 11:02:00 4 2019-02-01 11:13:00
250846 3091-5-007208 4 2019-02-01 11:13:00 NULL NULL
This will allow you to identify when the state has changed from 5 to 4. You just need to put the above query into a subquery, and apply a where clause:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t
) AS t
WHERE t.State = 5
AND t.NextState = 4;
If you are using an older version that does not support the use of LEAD, then you will need to use co-related subqueries to replicate the functionality:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
( SELECT TimeStamp
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) AS NextTimeStamp
FROM t
WHERE t.State = 5
AND ( SELECT State
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) = 4 -- next state = 4 to satisfy changing from 5 to 4
) AS t;
Examples on DB<>Fiddle
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
add a comment |
0
0
0
If you are using a later version of MySQL you can use the LEAD function to get the next state for each row
SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t;
Which for one device will give:
id device state timestamp NextState NextTimeStamp
---------------------------------------------------------------------------------------------
250795 3091-5-007208 7 2019-02-01 10:39:00 5 2019-02-01 10:42:00
250819 3091-5-007208 5 2019-02-01 10:42:00 4 2019-02-01 10:58:00 <---
250832 3091-5-007208 4 2019-02-01 10:58:00 11 2019-02-01 11:02:00
250837 3091-5-007208 11 2019-02-01 11:02:00 4 2019-02-01 11:13:00
250846 3091-5-007208 4 2019-02-01 11:13:00 NULL NULL
This will allow you to identify when the state has changed from 5 to 4. You just need to put the above query into a subquery, and apply a where clause:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t
) AS t
WHERE t.State = 5
AND t.NextState = 4;
If you are using an older version that does not support the use of LEAD, then you will need to use co-related subqueries to replicate the functionality:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
( SELECT TimeStamp
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) AS NextTimeStamp
FROM t
WHERE t.State = 5
AND ( SELECT State
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) = 4 -- next state = 4 to satisfy changing from 5 to 4
) AS t;
Examples on DB<>Fiddle
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
If you are using a later version of MySQL you can use the LEAD function to get the next state for each row
SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t;
Which for one device will give:
id device state timestamp NextState NextTimeStamp
---------------------------------------------------------------------------------------------
250795 3091-5-007208 7 2019-02-01 10:39:00 5 2019-02-01 10:42:00
250819 3091-5-007208 5 2019-02-01 10:42:00 4 2019-02-01 10:58:00 <---
250832 3091-5-007208 4 2019-02-01 10:58:00 11 2019-02-01 11:02:00
250837 3091-5-007208 11 2019-02-01 11:02:00 4 2019-02-01 11:13:00
250846 3091-5-007208 4 2019-02-01 11:13:00 NULL NULL
This will allow you to identify when the state has changed from 5 to 4. You just need to put the above query into a subquery, and apply a where clause:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM t
) AS t
WHERE t.State = 5
AND t.NextState = 4;
If you are using an older version that does not support the use of LEAD, then you will need to use co-related subqueries to replicate the functionality:
SELECT t.device,
t.TimeStamp AS ChangedToFive,
t.NextTimeStamp AS ChangedFromFiveToFour
FROM ( SELECT *,
( SELECT TimeStamp
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) AS NextTimeStamp
FROM t
WHERE t.State = 5
AND ( SELECT State
FROM t AS t2
WHERE t2.Device = t.Device
AND t2.TimeStamp > t.TimeStamp
ORDER BY TimeStamp
LIMIT 1
) = 4 -- next state = 4 to satisfy changing from 5 to 4
) AS t;
Examples on DB<>Fiddle
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
answered Jan 2 at 12:29
GarethDGarethD
53.9k66393
53.9k66393
add a comment |
add a comment |
0
You can use JOIN for this. The following assumes that there is exactly one 5->4 transition so does not bother checking if it was the first transition:
SELECT t1.id, t2.id, TIMESTAMPDIFF(SECOND, t1.timestamp,t2.timestamp) AS diff
FROM t AS t1
INNER JOIN t AS t2 ON t2.device = t1.device AND t2.state = 4 AND t2.timestamp > t1.timestamp
LEFT JOIN t AS tx ON t1.device = tx.device AND tx.timestamp > t1.timestamp AND tx.timestamp < t2.timestamp
WHERE t1.state = 5 AND tx.id IS NULL
The LEFT join in the above example ensures that only consecutive transition is matched (e.g. 250774 is joined to 250778 and 250808 but the latter is discarded).
Demo on db<>fiddle
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
add a comment |
0
You can use JOIN for this. The following assumes that there is exactly one 5->4 transition so does not bother checking if it was the first transition:
SELECT t1.id, t2.id, TIMESTAMPDIFF(SECOND, t1.timestamp,t2.timestamp) AS diff
FROM t AS t1
INNER JOIN t AS t2 ON t2.device = t1.device AND t2.state = 4 AND t2.timestamp > t1.timestamp
LEFT JOIN t AS tx ON t1.device = tx.device AND tx.timestamp > t1.timestamp AND tx.timestamp < t2.timestamp
WHERE t1.state = 5 AND tx.id IS NULL
The LEFT join in the above example ensures that only consecutive transition is matched (e.g. 250774 is joined to 250778 and 250808 but the latter is discarded).
Demo on db<>fiddle
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
add a comment |
0
0
0
You can use JOIN for this. The following assumes that there is exactly one 5->4 transition so does not bother checking if it was the first transition:
SELECT t1.id, t2.id, TIMESTAMPDIFF(SECOND, t1.timestamp,t2.timestamp) AS diff
FROM t AS t1
INNER JOIN t AS t2 ON t2.device = t1.device AND t2.state = 4 AND t2.timestamp > t1.timestamp
LEFT JOIN t AS tx ON t1.device = tx.device AND tx.timestamp > t1.timestamp AND tx.timestamp < t2.timestamp
WHERE t1.state = 5 AND tx.id IS NULL
The LEFT join in the above example ensures that only consecutive transition is matched (e.g. 250774 is joined to 250778 and 250808 but the latter is discarded).
Demo on db<>fiddle
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
You can use JOIN for this. The following assumes that there is exactly one 5->4 transition so does not bother checking if it was the first transition:
SELECT t1.id, t2.id, TIMESTAMPDIFF(SECOND, t1.timestamp,t2.timestamp) AS diff
FROM t AS t1
INNER JOIN t AS t2 ON t2.device = t1.device AND t2.state = 4 AND t2.timestamp > t1.timestamp
LEFT JOIN t AS tx ON t1.device = tx.device AND tx.timestamp > t1.timestamp AND tx.timestamp < t2.timestamp
WHERE t1.state = 5 AND tx.id IS NULL
The LEFT join in the above example ensures that only consecutive transition is matched (e.g. 250774 is joined to 250778 and 250808 but the latter is discarded).
Demo on db<>fiddle
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
edited Jan 3 at 7:18
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
answered Jan 2 at 13:33
Salman ASalman A
185k67346442
185k67346442
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
add a comment |
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
LEFT JOIN t AS tx to which table here t is considered ? Facing error
– bilal shakir
Jan 3 at 5:37
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
@bilal you're supposed to replace t with actual table name. See dbfiddle.uk/…
– Salman A
Jan 3 at 7:13
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
Thanks salman, This query worked for me perfectly. Thank you much. I am feeling happy.
– bilal shakir
Jan 3 at 8:04
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%2f54005768%2fhow-to-find-time-difference-for-state-change-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. Store dates as dates. 2. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 11:49
Incidentally, it seems a little odd (although not impossible) that ids aren't sequential with timetamps.
– Strawberry
Jan 2 at 11:53