Mixing
How to select multiple series from a certain timestamp in SQLite Query
I have an SQLite database containing event log records with a timestamp and the event details. Some of these events are alarms generated by the system. What I need to do can be best explained in the following pseudo code:
for each event where type="alarm" get preceeding events between event.timestamp - 1 hour and event.timestamp
What I do now is query the database once to find the timestamp of each alarm and then loop over the result to get all the time periods for each alarm. This creates a list of transactions that I want to use for association rule mining but I feel that leaving SQL to then come back to it in a loop is inefficient. I've searched for answers to this question but I think I can't find the right keywords to search for.
I've edited the question as it wasn't as clear as I myself thought
sqlite
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
add a comment |
I have an SQLite database containing event log records with a timestamp and the event details. Some of these events are alarms generated by the system. What I need to do can be best explained in the following pseudo code:
for each event where type="alarm" get preceeding events between event.timestamp - 1 hour and event.timestamp
What I do now is query the database once to find the timestamp of each alarm and then loop over the result to get all the time periods for each alarm. This creates a list of transactions that I want to use for association rule mining but I feel that leaving SQL to then come back to it in a loop is inefficient. I've searched for answers to this question but I think I can't find the right keywords to search for.
I've edited the question as it wasn't as clear as I myself thought
sqlite
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
What is n ? What do you mean by "next" rows --- with respect to which ordering ?
– Rolvernew
Nov 20 '18 at 15:05
add a comment |
I have an SQLite database containing event log records with a timestamp and the event details. Some of these events are alarms generated by the system. What I need to do can be best explained in the following pseudo code:
for each event where type="alarm" get preceeding events between event.timestamp - 1 hour and event.timestamp
What I do now is query the database once to find the timestamp of each alarm and then loop over the result to get all the time periods for each alarm. This creates a list of transactions that I want to use for association rule mining but I feel that leaving SQL to then come back to it in a loop is inefficient. I've searched for answers to this question but I think I can't find the right keywords to search for.
I've edited the question as it wasn't as clear as I myself thought
sqlite
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
I have an SQLite database containing event log records with a timestamp and the event details. Some of these events are alarms generated by the system. What I need to do can be best explained in the following pseudo code:
for each event where type="alarm" get preceeding events between event.timestamp - 1 hour and event.timestamp
What I do now is query the database once to find the timestamp of each alarm and then loop over the result to get all the time periods for each alarm. This creates a list of transactions that I want to use for association rule mining but I feel that leaving SQL to then come back to it in a loop is inefficient. I've searched for answers to this question but I think I can't find the right keywords to search for.
I've edited the question as it wasn't as clear as I myself thought
sqlite
sqlite
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
edited Nov 20 '18 at 18:04
Roel van Endhoven
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
asked Nov 20 '18 at 14:11
Roel van EndhovenRoel van Endhoven
4817
4817
What is n ? What do you mean by "next" rows --- with respect to which ordering ?
– Rolvernew
Nov 20 '18 at 15:05
add a comment |
What is n ? What do you mean by "next" rows --- with respect to which ordering ?
– Rolvernew
Nov 20 '18 at 15:05
What is n ? What do you mean by "next" rows --- with respect to which ordering ?
– Rolvernew
Nov 20 '18 at 15:05
What is n ? What do you mean by "next" rows --- with respect to which ordering ?
– Rolvernew
Nov 20 '18 at 15:05
add a comment |
1 Answer
1
active
oldest
votes
Perhaps a self-join?
SELECT {column list}
from events e1, events e2
where e1.type="alarm"
and e2.time between (date(e1.time),'-1 hour') and e1.time
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
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
});
}
});
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%2f53394904%2fhow-to-select-multiple-series-from-a-certain-timestamp-in-sqlite-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Perhaps a self-join?
SELECT {column list}
from events e1, events e2
where e1.type="alarm"
and e2.time between (date(e1.time),'-1 hour') and e1.time
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
add a comment |
Perhaps a self-join?
SELECT {column list}
from events e1, events e2
where e1.type="alarm"
and e2.time between (date(e1.time),'-1 hour') and e1.time
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
add a comment |
Perhaps a self-join?
SELECT {column list}
from events e1, events e2
where e1.type="alarm"
and e2.time between (date(e1.time),'-1 hour') and e1.time
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
Perhaps a self-join?
SELECT {column list}
from events e1, events e2
where e1.type="alarm"
and e2.time between (date(e1.time),'-1 hour') and e1.time
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
answered Nov 21 '18 at 13:49
DinoCoderSaurusDinoCoderSaurus
75958
75958
add a comment |
add a comment |
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.
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%2f53394904%2fhow-to-select-multiple-series-from-a-certain-timestamp-in-sqlite-query%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
What is n ? What do you mean by "next" rows --- with respect to which ordering ?
– Rolvernew
Nov 20 '18 at 15:05