Elastic how to aggregate hour on different days
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I would like to aggragate data on documetns which have different days e.g. the hours from 12 to 18 only on THURSDAY.
My query including aggregation looks like this:
{
"query": {
"bool": {
"must": [
{
"match": {
"locationid.raw": "HH-44-6"
}
},
{
"match": {
"day.keyword": "THURSDAY"
}
},
{
"range": {
"dataHour": {
"from": "12",
"to": "18",
"include_lower": true,
"include_upper": true
}
}
},
{
"range": {
"dataDate": {
"gte": "2018-11-08 12:00",
"include_lower": true
}
}
}
]
}
},
"aggs" : {
"contacts" : {
"date_histogram" : {
"field" : "dataDate",
"interval" : "hour"
},
"aggs": {
"seeing_data": {
"avg": {
"field": "seeing"
}
}
}
}
}
The response is to big because it gives the aggregates the data in the interval for every day and hour between the startdate of '2018-11-08 18:00' and now, instead of only the three available days (because from 2018-11-08 until now are only three THURSDAYS).
How can i achieve it to only aggregate data within the the hour range of 12-18 and only the THURSDAYS starting at 2018-11-08 12:00?
elasticsearch aggregation
asked Nov 23 '18 at 10:43
pietpiet
130110
add a comment |
I would like to aggragate data on documetns which have different days e.g. the hours from 12 to 18 only on THURSDAY.
My query including aggregation looks like this:
{
"query": {
"bool": {
"must": [
{
"match": {
"locationid.raw": "HH-44-6"
}
},
{
"match": {
"day.keyword": "THURSDAY"
}
},
{
"range": {
"dataHour": {
"from": "12",
"to": "18",
"include_lower": true,
"include_upper": true
}
}
},
{
"range": {
"dataDate": {
"gte": "2018-11-08 12:00",
"include_lower": true
}
}
}
]
}
},
"aggs" : {
"contacts" : {
"date_histogram" : {
"field" : "dataDate",
"interval" : "hour"
},
"aggs": {
"seeing_data": {
"avg": {
"field": "seeing"
}
}
}
}
}
The response is to big because it gives the aggregates the data in the interval for every day and hour between the startdate of '2018-11-08 18:00' and now, instead of only the three available days (because from 2018-11-08 until now are only three THURSDAYS).
How can i achieve it to only aggregate data within the the hour range of 12-18 and only the THURSDAYS starting at 2018-11-08 12:00?
elasticsearch aggregation
asked Nov 23 '18 at 10:43
pietpiet
130110
add a comment |
I would like to aggragate data on documetns which have different days e.g. the hours from 12 to 18 only on THURSDAY.
My query including aggregation looks like this:
{
"query": {
"bool": {
"must": [
{
"match": {
"locationid.raw": "HH-44-6"
}
},
{
"match": {
"day.keyword": "THURSDAY"
}
},
{
"range": {
"dataHour": {
"from": "12",
"to": "18",
"include_lower": true,
"include_upper": true
}
}
},
{
"range": {
"dataDate": {
"gte": "2018-11-08 12:00",
"include_lower": true
}
}
}
]
}
},
"aggs" : {
"contacts" : {
"date_histogram" : {
"field" : "dataDate",
"interval" : "hour"
},
"aggs": {
"seeing_data": {
"avg": {
"field": "seeing"
}
}
}
}
}
The response is to big because it gives the aggregates the data in the interval for every day and hour between the startdate of '2018-11-08 18:00' and now, instead of only the three available days (because from 2018-11-08 until now are only three THURSDAYS).
How can i achieve it to only aggregate data within the the hour range of 12-18 and only the THURSDAYS starting at 2018-11-08 12:00?
elasticsearch aggregation
asked Nov 23 '18 at 10:43
pietpiet
130110
I would like to aggragate data on documetns which have different days e.g. the hours from 12 to 18 only on THURSDAY.
My query including aggregation looks like this:
{
"query": {
"bool": {
"must": [
{
"match": {
"locationid.raw": "HH-44-6"
}
},
{
"match": {
"day.keyword": "THURSDAY"
}
},
{
"range": {
"dataHour": {
"from": "12",
"to": "18",
"include_lower": true,
"include_upper": true
}
}
},
{
"range": {
"dataDate": {
"gte": "2018-11-08 12:00",
"include_lower": true
}
}
}
]
}
},
"aggs" : {
"contacts" : {
"date_histogram" : {
"field" : "dataDate",
"interval" : "hour"
},
"aggs": {
"seeing_data": {
"avg": {
"field": "seeing"
}
}
}
}
}
The response is to big because it gives the aggregates the data in the interval for every day and hour between the startdate of '2018-11-08 18:00' and now, instead of only the three available days (because from 2018-11-08 until now are only three THURSDAYS).
How can i achieve it to only aggregate data within the the hour range of 12-18 and only the THURSDAYS starting at 2018-11-08 12:00?
elasticsearch aggregation
elasticsearch aggregation
asked Nov 23 '18 at 10:43
pietpiet
130110
asked Nov 23 '18 at 10:43
pietpiet
130110
asked Nov 23 '18 at 10:43
pietpiet
130110
asked Nov 23 '18 at 10:43
pietpiet
130110
asked Nov 23 '18 at 10:43
pietpiet
130110
130110
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You could use a script filter
"script": {
"script": "doc['@timestamp'].date.dayOfWeek == 2"
}
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
1
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
add a comment |
Get through theses steps to be able to aggregate your data by hours of a day :
So you have a date field in your document. You can't from that extract hours. So you have to create a custom field in Kibana.
- Go to the "Management" section
- Go to "Index patterns"
- Go to "Create index pattern"
- Choose your collection
- Go to the "Script fields" tab
- Click on "Add scripted field"
Now we will add the hour field :
- In the "name" field, enter "hour".
- Set the type to "number".
- And put in the "script" field :
doc['myDateField'].date.hourOfDay, wheremyDateFieldis a field with the date of your document.
There it is ! You can now find your new field in the Discover or Visualize sections.
Here, I aggregate the number of data I've received by hours :
Find more types of aggregation (for example, date.dayOfWeek) here :
https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-expression.html#_date_field_api
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
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%2f53445163%2felastic-how-to-aggregate-hour-on-different-days%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
You could use a script filter
"script": {
"script": "doc['@timestamp'].date.dayOfWeek == 2"
}
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
1
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
add a comment |
You could use a script filter
"script": {
"script": "doc['@timestamp'].date.dayOfWeek == 2"
}
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
1
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
add a comment |
You could use a script filter
"script": {
"script": "doc['@timestamp'].date.dayOfWeek == 2"
}
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
You could use a script filter
"script": {
"script": "doc['@timestamp'].date.dayOfWeek == 2"
}
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
answered Nov 23 '18 at 11:28
LeBigCatLeBigCat
666312
666312
1
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
add a comment |
1
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
1
1
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
It is not clear how to use this kind of script. Can you explain more, how to integrate it and which field/type are used for it?
– piet
Nov 23 '18 at 11:39
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
i modified the script a little bit cause "date" is deprecated and using value. Put it in the query, but it has no effect.
– piet
Nov 23 '18 at 12:36
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
Unclear answer. Please update to tell where we have to put that !
– Flavien B.
Mar 28 at 9:43
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
{ "query": { "bool" : { "filter" : { "script" : { "script" : { "source": "doc[yourdatefield].date.dayOfWeek == 2", "lang": "painless" } } } } } }
– LeBigCat
Mar 28 at 11:09
add a comment |
Get through theses steps to be able to aggregate your data by hours of a day :
So you have a date field in your document. You can't from that extract hours. So you have to create a custom field in Kibana.
- Go to the "Management" section
- Go to "Index patterns"
- Go to "Create index pattern"
- Choose your collection
- Go to the "Script fields" tab
- Click on "Add scripted field"
Now we will add the hour field :
- In the "name" field, enter "hour".
- Set the type to "number".
- And put in the "script" field :
doc['myDateField'].date.hourOfDay, wheremyDateFieldis a field with the date of your document.
There it is ! You can now find your new field in the Discover or Visualize sections.
Here, I aggregate the number of data I've received by hours :
Find more types of aggregation (for example, date.dayOfWeek) here :
https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-expression.html#_date_field_api
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
add a comment |
Get through theses steps to be able to aggregate your data by hours of a day :
So you have a date field in your document. You can't from that extract hours. So you have to create a custom field in Kibana.
- Go to the "Management" section
- Go to "Index patterns"
- Go to "Create index pattern"
- Choose your collection
- Go to the "Script fields" tab
- Click on "Add scripted field"
Now we will add the hour field :
- In the "name" field, enter "hour".
- Set the type to "number".
- And put in the "script" field :
doc['myDateField'].date.hourOfDay, wheremyDateFieldis a field with the date of your document.
There it is ! You can now find your new field in the Discover or Visualize sections.
Here, I aggregate the number of data I've received by hours :
Find more types of aggregation (for example, date.dayOfWeek) here :
https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-expression.html#_date_field_api
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
add a comment |
Get through theses steps to be able to aggregate your data by hours of a day :
So you have a date field in your document. You can't from that extract hours. So you have to create a custom field in Kibana.
- Go to the "Management" section
- Go to "Index patterns"
- Go to "Create index pattern"
- Choose your collection
- Go to the "Script fields" tab
- Click on "Add scripted field"
Now we will add the hour field :
- In the "name" field, enter "hour".
- Set the type to "number".
- And put in the "script" field :
doc['myDateField'].date.hourOfDay, wheremyDateFieldis a field with the date of your document.
There it is ! You can now find your new field in the Discover or Visualize sections.
Here, I aggregate the number of data I've received by hours :
Find more types of aggregation (for example, date.dayOfWeek) here :
https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-expression.html#_date_field_api
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
Get through theses steps to be able to aggregate your data by hours of a day :
So you have a date field in your document. You can't from that extract hours. So you have to create a custom field in Kibana.
- Go to the "Management" section
- Go to "Index patterns"
- Go to "Create index pattern"
- Choose your collection
- Go to the "Script fields" tab
- Click on "Add scripted field"
Now we will add the hour field :
- In the "name" field, enter "hour".
- Set the type to "number".
- And put in the "script" field :
doc['myDateField'].date.hourOfDay, wheremyDateFieldis a field with the date of your document.
There it is ! You can now find your new field in the Discover or Visualize sections.
Here, I aggregate the number of data I've received by hours :
Find more types of aggregation (for example, date.dayOfWeek) here :
https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-expression.html#_date_field_api
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
edited Mar 28 at 10:21
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
answered Mar 28 at 10:16
Flavien B.Flavien B.
95212
95212
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%2f53445163%2felastic-how-to-aggregate-hour-on-different-days%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
