Mixing
Get last stored value at a given time in influxDB
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm storing values of my temperature sensors in an influxDB database and I'm looking for a special request.
Each sensor sends sensed data when temperature changes with a certain threshold which means that all sensors do not send data at the same time.
So sensor 1, namely S1 will send value 1 (S1_v1) at instant t1. Then S2 will send S2_v2 at t2, S3 sends S3_v3 at t3, etc.
I'd like to have the values of all the sensors at a given time t so that at t2, the returned value of S1 will be S1_v1 (the last stored one).
How can I do that with influxDB please? I hope my request is enough clear.
Thank you very much.
influxdb
asked Dec 27 '18 at 15:02
radarradar
180313
add a comment |
I'm storing values of my temperature sensors in an influxDB database and I'm looking for a special request.
Each sensor sends sensed data when temperature changes with a certain threshold which means that all sensors do not send data at the same time.
So sensor 1, namely S1 will send value 1 (S1_v1) at instant t1. Then S2 will send S2_v2 at t2, S3 sends S3_v3 at t3, etc.
I'd like to have the values of all the sensors at a given time t so that at t2, the returned value of S1 will be S1_v1 (the last stored one).
How can I do that with influxDB please? I hope my request is enough clear.
Thank you very much.
influxdb
asked Dec 27 '18 at 15:02
radarradar
180313
add a comment |
I'm storing values of my temperature sensors in an influxDB database and I'm looking for a special request.
Each sensor sends sensed data when temperature changes with a certain threshold which means that all sensors do not send data at the same time.
So sensor 1, namely S1 will send value 1 (S1_v1) at instant t1. Then S2 will send S2_v2 at t2, S3 sends S3_v3 at t3, etc.
I'd like to have the values of all the sensors at a given time t so that at t2, the returned value of S1 will be S1_v1 (the last stored one).
How can I do that with influxDB please? I hope my request is enough clear.
Thank you very much.
influxdb
asked Dec 27 '18 at 15:02
radarradar
180313
I'm storing values of my temperature sensors in an influxDB database and I'm looking for a special request.
Each sensor sends sensed data when temperature changes with a certain threshold which means that all sensors do not send data at the same time.
So sensor 1, namely S1 will send value 1 (S1_v1) at instant t1. Then S2 will send S2_v2 at t2, S3 sends S3_v3 at t3, etc.
I'd like to have the values of all the sensors at a given time t so that at t2, the returned value of S1 will be S1_v1 (the last stored one).
How can I do that with influxDB please? I hope my request is enough clear.
Thank you very much.
influxdb
influxdb
asked Dec 27 '18 at 15:02
radarradar
180313
asked Dec 27 '18 at 15:02
radarradar
180313
asked Dec 27 '18 at 15:02
radarradar
180313
asked Dec 27 '18 at 15:02
radarradar
180313
asked Dec 27 '18 at 15:02
radarradar
180313
180313
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can store all your sensor data into one measurement.
Then have a tag call name to store the sensor's name.
Example:
> select * from sensors;
name: sensors
time name value
---- ---- -----
1547100000000000000 s1 500
1547200000000000000 s2 600
1547300000000000000 s3 700
1548000000000000000 s1 900
1548000000000000000 s2 800
1548000000000000000 s3 999
To retrieve the latest stored value of all the sensors at a given time range t you can do the following;
SELECT * FROM sensors
WHERE time >= 1547000000000000000 and time <= 1547300000000000000
GROUP BY "name" order by desc limit 1;
Output:
name: sensors
tags: name=s3
time value
---- -----
1547300000000000000 700
name: sensors
tags: name=s2
time value
---- -----
1547200000000000000 600
name: sensors
tags: name=s1
time value
---- -----
1547100000000000000 500
The query above is essentially grouping the data of all your sensors into individual bucket based on your time filter. Then the ORDER BY DESC is for sorting them into descending order so that the first row is always the point with greatest time. Limit 1 is just asking the query engine to return you the top 1 row.
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Yes. Instead of giving it a range in the filter statement, do onlyWHERE time <= t. wheretis your time value.
– Samuel Toh
Jan 3 at 22:48
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
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%2f53947034%2fget-last-stored-value-at-a-given-time-in-influxdb%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
You can store all your sensor data into one measurement.
Then have a tag call name to store the sensor's name.
Example:
> select * from sensors;
name: sensors
time name value
---- ---- -----
1547100000000000000 s1 500
1547200000000000000 s2 600
1547300000000000000 s3 700
1548000000000000000 s1 900
1548000000000000000 s2 800
1548000000000000000 s3 999
To retrieve the latest stored value of all the sensors at a given time range t you can do the following;
SELECT * FROM sensors
WHERE time >= 1547000000000000000 and time <= 1547300000000000000
GROUP BY "name" order by desc limit 1;
Output:
name: sensors
tags: name=s3
time value
---- -----
1547300000000000000 700
name: sensors
tags: name=s2
time value
---- -----
1547200000000000000 600
name: sensors
tags: name=s1
time value
---- -----
1547100000000000000 500
The query above is essentially grouping the data of all your sensors into individual bucket based on your time filter. Then the ORDER BY DESC is for sorting them into descending order so that the first row is always the point with greatest time. Limit 1 is just asking the query engine to return you the top 1 row.
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Yes. Instead of giving it a range in the filter statement, do onlyWHERE time <= t. wheretis your time value.
– Samuel Toh
Jan 3 at 22:48
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
add a comment |
You can store all your sensor data into one measurement.
Then have a tag call name to store the sensor's name.
Example:
> select * from sensors;
name: sensors
time name value
---- ---- -----
1547100000000000000 s1 500
1547200000000000000 s2 600
1547300000000000000 s3 700
1548000000000000000 s1 900
1548000000000000000 s2 800
1548000000000000000 s3 999
To retrieve the latest stored value of all the sensors at a given time range t you can do the following;
SELECT * FROM sensors
WHERE time >= 1547000000000000000 and time <= 1547300000000000000
GROUP BY "name" order by desc limit 1;
Output:
name: sensors
tags: name=s3
time value
---- -----
1547300000000000000 700
name: sensors
tags: name=s2
time value
---- -----
1547200000000000000 600
name: sensors
tags: name=s1
time value
---- -----
1547100000000000000 500
The query above is essentially grouping the data of all your sensors into individual bucket based on your time filter. Then the ORDER BY DESC is for sorting them into descending order so that the first row is always the point with greatest time. Limit 1 is just asking the query engine to return you the top 1 row.
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Yes. Instead of giving it a range in the filter statement, do onlyWHERE time <= t. wheretis your time value.
– Samuel Toh
Jan 3 at 22:48
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
add a comment |
You can store all your sensor data into one measurement.
Then have a tag call name to store the sensor's name.
Example:
> select * from sensors;
name: sensors
time name value
---- ---- -----
1547100000000000000 s1 500
1547200000000000000 s2 600
1547300000000000000 s3 700
1548000000000000000 s1 900
1548000000000000000 s2 800
1548000000000000000 s3 999
To retrieve the latest stored value of all the sensors at a given time range t you can do the following;
SELECT * FROM sensors
WHERE time >= 1547000000000000000 and time <= 1547300000000000000
GROUP BY "name" order by desc limit 1;
Output:
name: sensors
tags: name=s3
time value
---- -----
1547300000000000000 700
name: sensors
tags: name=s2
time value
---- -----
1547200000000000000 600
name: sensors
tags: name=s1
time value
---- -----
1547100000000000000 500
The query above is essentially grouping the data of all your sensors into individual bucket based on your time filter. Then the ORDER BY DESC is for sorting them into descending order so that the first row is always the point with greatest time. Limit 1 is just asking the query engine to return you the top 1 row.
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
You can store all your sensor data into one measurement.
Then have a tag call name to store the sensor's name.
Example:
> select * from sensors;
name: sensors
time name value
---- ---- -----
1547100000000000000 s1 500
1547200000000000000 s2 600
1547300000000000000 s3 700
1548000000000000000 s1 900
1548000000000000000 s2 800
1548000000000000000 s3 999
To retrieve the latest stored value of all the sensors at a given time range t you can do the following;
SELECT * FROM sensors
WHERE time >= 1547000000000000000 and time <= 1547300000000000000
GROUP BY "name" order by desc limit 1;
Output:
name: sensors
tags: name=s3
time value
---- -----
1547300000000000000 700
name: sensors
tags: name=s2
time value
---- -----
1547200000000000000 600
name: sensors
tags: name=s1
time value
---- -----
1547100000000000000 500
The query above is essentially grouping the data of all your sensors into individual bucket based on your time filter. Then the ORDER BY DESC is for sorting them into descending order so that the first row is always the point with greatest time. Limit 1 is just asking the query engine to return you the top 1 row.
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
answered Jan 3 at 12:24
Samuel TohSamuel Toh
8,64331219
8,64331219
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Yes. Instead of giving it a range in the filter statement, do onlyWHERE time <= t. wheretis your time value.
– Samuel Toh
Jan 3 at 22:48
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
add a comment |
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Yes. Instead of giving it a range in the filter statement, do onlyWHERE time <= t. wheretis your time value.
– Samuel Toh
Jan 3 at 22:48
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Thank you very much. Actually, I'd like to have a request where I specify one specific timestamp and get all last stored values at this moment. Not providing a time range. Do you think that's possible?
– radar
Jan 3 at 15:52
Yes. Instead of giving it a range in the filter statement, do only
WHERE time <= t. where t is your time value.– Samuel Toh
Jan 3 at 22:48
Yes. Instead of giving it a range in the filter statement, do only
WHERE time <= t. where t is your time value.– Samuel Toh
Jan 3 at 22:48
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
You're absolutely right. Thank you very much.
– radar
Jan 7 at 15:24
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%2f53947034%2fget-last-stored-value-at-a-given-time-in-influxdb%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
