# SQL average for period of day

**URL:** <https://discuss.elastic.co/t/sql-average-for-period-of-day/195474>\
**Category:** Kibana\
**Tags:** elastic-stack-sql, canvas\
**Created:** [August 16, 2019, 10:27am UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474 "2019-08-16T10:27:50Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![samgs](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@samgs](https://discuss.elastic.co/u/samgs)\
**Post date:** [August 16, 2019, 10:27am UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/1 "2019-08-16T10:27:50Z")

</div>

Hello,

I have a line graph that shows number of logins today. What I want to do is overlay the average number of logins over the last 30 days. Is that possible? I figure I'll have to use another graph element on canvas and put them on top of each other, but I cant figure out what the SQL would be.

This is my current SQL for today:

> SELECT name, data, date FROM index WHERE QUERY('name:visit') AND date \> TODAY() ORDER BY date

That give me data from 00:00 to what ever time it is right now. So I only want averages for that period too, say 00:00 - 8am.

Is this possible?

---

<div class="post-metadata">

**Author:** ![bhavyarm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavyarm/32/22392_2.png) [@bhavyarm](https://discuss.elastic.co/u/bhavyarm)\
**Post date:** [August 16, 2019, 4:32pm UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/2 "2019-08-16T16:32:57Z")

</div>

@tims @Catherine_Liu can we please get some help here please?

Thanks,  
Bhavya

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [August 16, 2019, 4:52pm UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/3 "2019-08-16T16:52:34Z")

</div>

@samgs There's a couple of options to try and get this done. I would suggest maybe trying to do this with the timelion expressions because you can set the time interval dynamically. Here's a tutorial: [https://www.elastic.co/blog/timelion-tutorial-from-zero-to-hero](https://www.elastic.co/blog/timelion-tutorial-from-zero-to-hero)

You can also use the math function in the Canvas expression language to get the average if you have a datatable of counts. Here's the documentation for that: [https://www.elastic.co/guide/en/kibana/master/canvas-tinymath-functions.html#\_mean\_8230\_args\_2](https://www.elastic.co/guide/en/kibana/master/canvas-tinymath-functions.html#_mean_8230_args_2)

---

<div class="post-metadata">

**Author:** ![samgs](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@samgs](https://discuss.elastic.co/u/samgs)\
**Post date:** [August 20, 2019, 8:13am UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/4 "2019-08-20T08:13:29Z")

</div>

Appreciate the answers here, but struggling to get either to work.

qq- should timelion work in canvas? Everytime I try anything other than the standard query

`.es(q=*, index=logstash-*)`

i get:

> [timelion] \> Request failed with status code 500

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [August 20, 2019, 2:48pm UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/5 "2019-08-20T14:48:22Z")

</div>

Yes, Timelion should work in Canvas. I'm going to open a ticket to unmask the error messages we are getting back as I have also noticed recently that this is a very unhelpful message. If you post your query we can take a look and see if we notice what the error could be.

[edit]  
Ticket opened here: [https://github.com/elastic/kibana/issues/43583](https://github.com/elastic/kibana/issues/43583)

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [August 29, 2019, 12:17am UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/6 "2019-08-29T00:17:35Z")

</div>

@samgs You might be able to achieve what you want using ES SQL if I've got your problem right.

Here's an example using the kibana flights sample data that displays the number of flights per day between 00:00 and 08:00 for the past 30 days:

![52%20PM](https://us1.discourse-cdn.com/elastic/original/3X/d/8/d89d6b12e507373237dc286f84f9ad25709aa951.png)

```auto
filters
| essql 
  query="SELECT Histogram(timestamp, INTERVAL 1 DAY) as date, count(*) as number_of_flights 
    FROM \"kibana_sample_data_flights\" 
    WHERE timestamp < TODAY() 
    AND timestamp >= TODAY() - INTERVAL 30 DAYS 
    AND HOUR_OF_DAY(timestamp) >= 0 
    AND HOUR_OF_DAY(timestamp) <= 8 
    GROUP BY date
    ORDER BY date" 
| table 
| render  

```

---

<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:** [September 26, 2019, 12:17am UTC](https://discuss.elastic.co/t/sql-average-for-period-of-day/195474/7 "2019-09-26T00:17:36Z")

</div>

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