# Showing data hourly

**URL:** <https://discuss.elastic.co/t/showing-data-hourly/278777>\
**Category:** Kibana\
**Created:** [July 15, 2021, 11:51am UTC](https://discuss.elastic.co/t/showing-data-hourly/278777 "2021-07-15T11:51:31Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![prathamesh7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prathamesh7/32/99920_2.png) [@prathamesh7](https://discuss.elastic.co/u/prathamesh7)\
**Post date:** [July 15, 2021, 11:51am UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/1 "2021-07-15T11:51:31Z")

</div>

Hi Team,

I need to display results of filter i am running in visualisation to show for each hour of a day.

When i run filter, i can see date for today but not each hour of day , it is showing with gap of 3 hours, so my question is how can i show all the hours of a day (0,1,2,..23) with their values.

 ![Screenshot 2021-07-15 at 5.12.40 PM](https://us1.discourse-cdn.com/elastic/original/3X/1/2/124685dfa3859d4c2c30900132b1310fa8f88cb1.png)

How can i achieve this in best way?

Do i need to use scripted field something like below?  
Using this gives error. I do not have date field.

```auto
 doc['@timestamp'].date.hourOfDay

```

Thanks,

---

<div class="post-metadata">

**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\
**Post date:** [July 15, 2021, 12:08pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/2 "2021-07-15T12:08:37Z")

</div>

You can create a scripted field (or runtime field in more recent kibana versions).

So for example I have created this scripted field:

`doc['timestamp'].value.hourOfDay`

and I have created the following chart

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/0/f037c6635a157ea9237be3e13fdaeaa2a28a6106.png)

---

<div class="post-metadata">

**Author:** ![prathamesh7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prathamesh7/32/99920_2.png) [@prathamesh7](https://discuss.elastic.co/u/prathamesh7)\
**Post date:** [July 15, 2021, 12:36pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/3 "2021-07-15T12:36:16Z")

</div>

Hello @Stratoula_Kalafateli, Thanks for your reply.

Created scripted field as below.

 ![Screenshot 2021-07-15 at 5.41.33 PM](https://us1.discourse-cdn.com/elastic/original/3X/b/f/bf70ebf901172ad26f11778e045ee379a7f10438.png)

I got below error in discover tab when i added `doc['@timestamp'].date.hourOfDay` scripted field. Also it only shows the scripted field and not any other fields in Discover and no data is displayed.

 ![Screenshot 2021-07-15 at 5.57.53 PM](https://us1.discourse-cdn.com/elastic/original/3X/a/4/a4f939f1ad4aa813e5cbae009ce6bb8dc5d7444d.png)

Below is the response from above error.

```auto
{
  "took": 79,
  "timed_out": false,
  "_shards": {
    "total": 15,
    "successful": 14,
    "skipped": 0,
    "failed": 1,
    "failures": [
      {
        "shard": 0,
        "index": "access_server-2021.07.15",
        "node": "oygWDlmkUJN6lHnw",
        "reason": {
          "type": "script_exception",
          "reason": "runtime error",
          "script_stack": [
            "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:94)",
            "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:41)",
            "doc['timestamp'].value.hourOfDay",
            " ^---- HERE"
          ],
          "script": "doc['timestamp'].value.hourOfDay",
          "lang": "painless",
          "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "No field found for [timestamp] in mapping with types []"
          }
        }
      }
    ]
  },
  "hits": {
    "total": 72,
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "2": {
      "buckets": [
        {
          "key_as_string": "2021-07-15T10:00:00.000+05:30",
          "key": 1626323400000,
          "doc_count": 72
        }
      ]
    }
  },
  "status": 200
}

```

Before adding scripted field i can see data in discover,

 ![Screenshot 2021-07-15 at 5.46.58 PM](https://us1.discourse-cdn.com/elastic/original/3X/1/8/18250013dfdcf2147fccd849fa901440579f29c1.png)

but after adding scripted field it shows no fields to choose and no data.

 ![Screenshot 2021-07-15 at 6.08.14 PM](https://us1.discourse-cdn.com/elastic/original/3X/1/d/1d9f689620bea3a613967c6e52469a6fd0cae2c0.png)

Thanks,

---

<div class="post-metadata">

**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\
**Post date:** [July 15, 2021, 12:39pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/4 "2021-07-15T12:39:07Z")

</div>

I think that it fails because your timefield is not `timestamp` but `@timestamp`. So can you please try changing your script to `doc['@timestamp'].value.hourOfDay`?

`

---

<div class="post-metadata">

**Author:** ![prathamesh7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prathamesh7/32/99920_2.png) [@prathamesh7](https://discuss.elastic.co/u/prathamesh7)\
**Post date:** [July 15, 2021, 12:54pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/5 "2021-07-15T12:54:10Z")

</div>

Hello @Stratoula_Kalafateli, Thanks for your quick reply.

After changing scripted field as said it is not giving error now. I did aggregation same as you, but i cannot see `auto` option in `Minimum interval` (v 7.4) so i choose it to 1 and i am getting below.

 ![Screenshot 2021-07-15 at 6.14.13 PM](https://us1.discourse-cdn.com/elastic/original/3X/5/4/548e6f5552b8c1b91787e04d44879c9ccaf70866.png)

Data is available only between 4 AM UTC so its showing in graph, however i still can't see all the hours of a day with their whatever values, that is something like below

```auto
Hrs total hits

0: 23
1: 0
2: 35
3: 15
4: 72
.
.

```

Thanks,

---

<div class="post-metadata">

**Author:** ![prathamesh7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prathamesh7/32/99920_2.png) [@prathamesh7](https://discuss.elastic.co/u/prathamesh7)\
**Post date:** [July 15, 2021, 5:46pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/6 "2021-07-15T17:46:33Z")

</div>

Hi @Stratoula_Kalafateli,

I am able to get the desired results by selecting `Show empty buckets` and `Extends bounds` between 0 to 23

 ![Screenshot 2021-07-15 at 11.03.35 PM](https://us1.discourse-cdn.com/elastic/original/3X/4/b/4b6709411c277fde402183ab2e2bcae55b5b0f2d.png)

curl works fine in `dev_tools`, although sometimes gives me `#! Deprecation: Use of the joda time method [getHourOfDay()] is deprecated. Use [getHour()] instead.` but same curl is not working from outside kibana i.e from server.

curl from kibana shows results properly

```auto
GET /access*/_search?pretty
{
  "aggs": {
    "2": {
      "histogram": {
        "script": {
          "source": "doc['@timestamp'].value.hourOfDay",
          "lang": "painless"
        },
        "interval": 1,
        "min_doc_count": 0,
        "extended_bounds": {
          "min": 0,
          "max": 23
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {
    "custom_hour": {
      "script": {
        "source": "doc['@timestamp'].value.hourOfDay",
        "lang": "painless"
      }
    }
  },
  "docvalue_fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "bool": {
            "should": [
              {
                "match_phrase": {
                  "log.file.path": "/access-1.0/log/access_audit.log"
                }
              }
            ],
            "minimum_should_match": 1
          }
        },
        {
          "match_phrase": {
            "Request_URI": {
              "query": "\"/next2-isp/v1/*\""
            }
          }
        },
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2021-07-14T18:30:00.000Z",
              "lte": "2021-07-15T18:29:59.999Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

```

same above curl (just by adding `curl -u elastic:xxxx -XGET "http://localhost:9200/access*/_search?pretty" -H 'Content-Type: application/json' -d'`) is giving below error,

```auto
{
  "error" : {
    "root_cause" : [
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "doc[@timestamp].value.hourOfD ...",
          " ^---- HERE"
        ],
        "script" : "doc[@timestamp].value.hourOfDay",
        "lang" : "painless"
      },
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "doc[@timestamp].value.hourOfD ...",
          " ^---- HERE"
        ],
        "script" : "doc[@timestamp].value.hourOfDay",
        "lang" : "painless"
      },
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "doc[@timestamp].value.hourOfD ...",
          " ^---- HERE"
        ],
        "script" : "doc[@timestamp].value.hourOfDay",
        "lang" : "painless"
      },
      {
   .
   .
   .
   .
"type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "access_server-2021.06.30",
        "node" : "oygWDlpVJN6lHTYnw",
        "reason" : {
          "type" : "script_exception",
          "reason" : "compile error",
          "script_stack" : [
            "doc[@timestamp].value.hourOfD ...",
            " ^---- HERE"
          ],
          "script" : "doc[@timestamp].value.hourOfDay",
          "lang" : "painless",
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "unexpected character [@].",
            "caused_by" : {
              "type" : "lexer_no_viable_alt_exception",
              "reason" : null
            }
          }
        }
      },
      {
        "shard" : 0,
        "index" : "access_server-2021.07.01",
        "node" : "oygWDlpV6lHTYnw",
        "reason" : {
          "type" : "script_exception",
          "reason" : "compile error",
          "script_stack" : [
            "doc[@timestamp].value.hourOfD ...",
            " ^---- HERE"
          ],
          "script" : "doc[@timestamp].value.hourOfDay",
          "lang" : "painless",
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "unexpected character [@].",
            "caused_by" : {
              "type" : "lexer_no_viable_alt_exception",
              "reason" : null
            }

```

Thanks,

---

<div class="post-metadata">

**Author:** ![prathamesh7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prathamesh7/32/99920_2.png) [@prathamesh7](https://discuss.elastic.co/u/prathamesh7)\
**Post date:** [July 15, 2021, 8:52pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/7 "2021-07-15T20:52:23Z")

</div>

Hi All,

Can someone please reply.

Thanks,

---

<div class="post-metadata">

**Author:** ![prathamesh7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prathamesh7/32/99920_2.png) [@prathamesh7](https://discuss.elastic.co/u/prathamesh7)\
**Post date:** [July 16, 2021, 3:38pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/8 "2021-07-16T15:38:22Z")

</div>

able to solve above error by replacing `'` with `\u0027`.

```auto
"source": "doc[\u0027@timestamp\u0027].value.hourOfDay",

```

Thanks,

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 13, 2021, 3:39pm UTC](https://discuss.elastic.co/t/showing-data-hourly/278777/9 "2021-08-13T15:39:22Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.