# CANVAS SQL: condition with timestamp

**URL:** <https://discuss.elastic.co/t/canvas-sql-condition-with-timestamp/259282>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [December 21, 2020, 4:00pm UTC](https://discuss.elastic.co/t/canvas-sql-condition-with-timestamp/259282 "2020-12-21T16:00:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TheHunter1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thehunter1/32/80190_2.png) [@TheHunter1](https://discuss.elastic.co/u/TheHunter1)\
**Post date:** [December 21, 2020, 4:00pm UTC](https://discuss.elastic.co/t/canvas-sql-condition-with-timestamp/259282/1 "2020-12-21T16:00:22Z")

</div>

Hello,

I am creating a canvas where I would like to show some anomalies in my cluster, and I wanna create something that show me if a new user in my AD has been created between midnight and 7am for example.

I have this SQL which allow me to see the number of users created, but I don't know how to add the timestamp condition:

```auto
SELECT COUNT(*) as user_added 
FROM "winlogbeat-*" 
WHERE "event.code" = 4720

```

Could you please help me ?  
Thanks 🙂

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [December 22, 2020, 4:29pm UTC](https://discuss.elastic.co/t/canvas-sql-condition-with-timestamp/259282/2 "2020-12-22T16:29:22Z")

</div>

Over all time? Or only over the last X days?

There might be several ways to do this, but sounds like you need an "hour of day" kind of filter. Seems like this would be best handled by an upcoming feature in 7.11 called "Runtime fields" which will give you the capability to define arbitrary new fields that are not in the mappings.

 ![runtimefield_editor_1](https://us1.discourse-cdn.com/elastic/original/3X/e/a/eaa3e931171352ff1476c8676888248a1406c394.gif)

The above example creates "day of week", but certainly hour of day would just be as easy.

But, it might be about a month until 7.11 is released. In the meantime, you could investigate using a [Transform with a scripted metric aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-painless-examples.html#painless-time-features) (to create an "hour of day" field) and have the transform put this in a new index - one that you query from Canvas.

---

<div class="post-metadata">

**Author:** ![TheHunter1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thehunter1/32/80190_2.png) [@TheHunter1](https://discuss.elastic.co/u/TheHunter1)\
**Post date:** [December 23, 2020, 8:03am UTC](https://discuss.elastic.co/t/canvas-sql-condition-with-timestamp/259282/3 "2020-12-23T08:03:19Z")

</div>

Thanks for your answer @richcollier, I will try that with transform as you suggested.

---

<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:** [January 20, 2021, 8:03am UTC](https://discuss.elastic.co/t/canvas-sql-condition-with-timestamp/259282/4 "2021-01-20T08:03:28Z")

</div>

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