# How to write query in Dev tools to find expired date of customers by comparing current date?

**URL:** <https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663>\
**Category:** Kibana\
**Created:** [June 11, 2021, 9:29am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663 "2021-06-11T09:29:33Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![padamrai](https://avatars.discourse-cdn.com/v4/letter/p/3da27b/32.png) [@padamrai](https://discuss.elastic.co/u/padamrai)\
**Post date:** [June 11, 2021, 9:29am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663/1 "2021-06-11T09:29:33Z")

</div>

I have a index that contains date field of customers that contains date of customers to be expired. Now my task is you to compare that date with current date(dynamically) changes as current date change gives result "expired" or "not expired" in new field.

let suppose expiry date is 2020-05-28 compare with current date returns "expired" in corresponding field.  
let suppose expiry date is 2021-06-11 and current date is same then shows "not expired" but tomorrow current date change to 2021-06-12 now that field automatically change from "not Expired" to expired.

I want to visualize on the dashboard after performing these task and expired status changes automatically date changes.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 15, 2021, 9:04am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663/2 "2021-06-15T09:04:54Z")

</div>

What exactly do you want to visualize?

If this is about the count of expired / non-expired customers, you can use filters for this:

 ![Screenshot 2021-06-15 at 11.03.12](https://us1.discourse-cdn.com/elastic/original/3X/3/3/334c39bfdbea33f2c3c8c63cddf42f2e6ca14642.png)

This is a lens table splitting up the documents by whether the `@timestamp` field is larger or smaller than "now" (which is the current time)

---

<div class="post-metadata">

**Author:** ![padamrai](https://avatars.discourse-cdn.com/v4/letter/p/3da27b/32.png) [@padamrai](https://discuss.elastic.co/u/padamrai)\
**Post date:** [June 15, 2021, 9:23am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663/3 "2021-06-15T09:23:03Z")

</div>

> [@flash1293](#):
>
> @timestamp

I have column name expiry\_date that contains dates to be expired. I want to compare these dates with current date and visualize on the PIE Chart. If the date is expired then count expired if not then count "not Expired". If date is not defined then "N/A".  
 ![cnic](https://us1.discourse-cdn.com/elastic/original/3X/b/a/ba5a3980c89e1c0335ac3af0a80e82c7bab1f8db.png)

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 15, 2021, 9:28am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663/4 "2021-06-15T09:28:58Z")

</div>

`@timestamp` was an example because my data set doesn't have an `expiry_date` field.

For the pie chart use a filters aggregation for the buckets and use these three KQL filters:

- `expiry_date > now` (label "Active")
- `expiry_date <= now` (label "Inactive")
- `NOT expiry_date: *` (label "N/A")

For the metric use count

---

<div class="post-metadata">

**Author:** ![padamrai](https://avatars.discourse-cdn.com/v4/letter/p/3da27b/32.png) [@padamrai](https://discuss.elastic.co/u/padamrai)\
**Post date:** [June 15, 2021, 9:32am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663/5 "2021-06-15T09:32:08Z")

</div>

Okay sir let me try this.

---

<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:** [July 13, 2021, 9:32am UTC](https://discuss.elastic.co/t/how-to-write-query-in-dev-tools-to-find-expired-date-of-customers-by-comparing-current-date/275663/6 "2021-07-13T09:32:11Z")

</div>

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