query result in set of interval ranges in postgresql(rails)
0
I have a timestamp column for which i have to calculate the time difference and divide it into certain set of intervals
for time difference in hours i have written this query
result = ActiveRecord::Base.connection.exec_query("SELECT id,(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - image_retouch_items.created_at)/3600)::INTEGER AS latency FROM image_retouch_items WHERE status= 0;");
The result of my query is
"id" "latency"
104 5928
106 5917
158 5751
162 5736
95 5940
85 5950
How to get result as set of intervals(hours),like for row for which time difference lie between the range of 0-24 hr increment the count .
i.e.
interval count
0-24 2
24-48 3
48-72 0
How to get that in single query
ruby-on-rails postgresql ruby-on-rails-3 activerecord
asked Nov 28 '18 at 3:28
summusummu
165111
add a comment |
0
I have a timestamp column for which i have to calculate the time difference and divide it into certain set of intervals
for time difference in hours i have written this query
result = ActiveRecord::Base.connection.exec_query("SELECT id,(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - image_retouch_items.created_at)/3600)::INTEGER AS latency FROM image_retouch_items WHERE status= 0;");
The result of my query is
"id" "latency"
104 5928
106 5917
158 5751
162 5736
95 5940
85 5950
How to get result as set of intervals(hours),like for row for which time difference lie between the range of 0-24 hr increment the count .
i.e.
interval count
0-24 2
24-48 3
48-72 0
How to get that in single query
ruby-on-rails postgresql ruby-on-rails-3 activerecord
asked Nov 28 '18 at 3:28
summusummu
165111
This might be helpful: stackoverflow.com/a/10607102/2640181
– barmic
Nov 28 '18 at 6:26
You can do something like thisSELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);
– Vishal
Nov 28 '18 at 6:39
@Vishal This doesn't work, as i have run the above query which i put in theresultvariable, to the result which you can can see in my updated question while using this query how would I write your generate_series part
– summu
Dec 1 '18 at 13:58
add a comment |
0
0
0
I have a timestamp column for which i have to calculate the time difference and divide it into certain set of intervals
for time difference in hours i have written this query
result = ActiveRecord::Base.connection.exec_query("SELECT id,(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - image_retouch_items.created_at)/3600)::INTEGER AS latency FROM image_retouch_items WHERE status= 0;");
The result of my query is
"id" "latency"
104 5928
106 5917
158 5751
162 5736
95 5940
85 5950
How to get result as set of intervals(hours),like for row for which time difference lie between the range of 0-24 hr increment the count .
i.e.
interval count
0-24 2
24-48 3
48-72 0
How to get that in single query
ruby-on-rails postgresql ruby-on-rails-3 activerecord
asked Nov 28 '18 at 3:28
summusummu
165111
I have a timestamp column for which i have to calculate the time difference and divide it into certain set of intervals
for time difference in hours i have written this query
result = ActiveRecord::Base.connection.exec_query("SELECT id,(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - image_retouch_items.created_at)/3600)::INTEGER AS latency FROM image_retouch_items WHERE status= 0;");
The result of my query is
"id" "latency"
104 5928
106 5917
158 5751
162 5736
95 5940
85 5950
How to get result as set of intervals(hours),like for row for which time difference lie between the range of 0-24 hr increment the count .
i.e.
interval count
0-24 2
24-48 3
48-72 0
How to get that in single query
ruby-on-rails postgresql ruby-on-rails-3 activerecord
ruby-on-rails postgresql ruby-on-rails-3 activerecord
asked Nov 28 '18 at 3:28
summusummu
165111
asked Nov 28 '18 at 3:28
summusummu
165111
edited Dec 1 '18 at 13:55
summu
asked Nov 28 '18 at 3:28
summusummu
165111
asked Nov 28 '18 at 3:28
summusummu
165111
asked Nov 28 '18 at 3:28
summusummu
165111
165111
This might be helpful: stackoverflow.com/a/10607102/2640181
– barmic
Nov 28 '18 at 6:26
You can do something like thisSELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);
– Vishal
Nov 28 '18 at 6:39
@Vishal This doesn't work, as i have run the above query which i put in theresultvariable, to the result which you can can see in my updated question while using this query how would I write your generate_series part
– summu
Dec 1 '18 at 13:58
add a comment |
This might be helpful: stackoverflow.com/a/10607102/2640181
– barmic
Nov 28 '18 at 6:26
You can do something like thisSELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);
– Vishal
Nov 28 '18 at 6:39
@Vishal This doesn't work, as i have run the above query which i put in theresultvariable, to the result which you can can see in my updated question while using this query how would I write your generate_series part
– summu
Dec 1 '18 at 13:58
This might be helpful: stackoverflow.com/a/10607102/2640181
– barmic
Nov 28 '18 at 6:26
This might be helpful: stackoverflow.com/a/10607102/2640181
– barmic
Nov 28 '18 at 6:26
You can do something like this
SELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);– Vishal
Nov 28 '18 at 6:39
You can do something like this
SELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);– Vishal
Nov 28 '18 at 6:39
@Vishal This doesn't work, as i have run the above query which i put in the
result variable, to the result which you can can see in my updated question while using this query how would I write your generate_series part– summu
Dec 1 '18 at 13:58
@Vishal This doesn't work, as i have run the above query which i put in the
result variable, to the result which you can can see in my updated question while using this query how would I write your generate_series part– summu
Dec 1 '18 at 13:58
add a comment |
0
active
oldest
votes
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%2f53511666%2fquery-result-in-set-of-interval-ranges-in-postgresqlrails%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53511666%2fquery-result-in-set-of-interval-ranges-in-postgresqlrails%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
This might be helpful: stackoverflow.com/a/10607102/2640181
– barmic
Nov 28 '18 at 6:26
You can do something like this
SELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);– Vishal
Nov 28 '18 at 6:39
@Vishal This doesn't work, as i have run the above query which i put in the
resultvariable, to the result which you can can see in my updated question while using this query how would I write your generate_series part– summu
Dec 1 '18 at 13:58