# Differences in values between Dev Tools SQL and Visualization

**URL:** https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955
**Category:** Kibana
**Tags:** elastic-stack-sql
**Created:** [May 13, 2021, 8:41pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955 "2021-05-13T20:41:41Z")
**Posts on this page:** 6
**Page:** 1

<div class="post-metadata">

### Author: ![Christopher\_Mello](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christopher_mello/32/88736_2.png) [@Christopher\_Mello](https://discuss.elastic.co/u/Christopher_Mello)
#### Post date: [May 13, 2021, 8:41pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955/1 "2021-05-13T20:41:41Z")

</div>

Hi all,

I am new to elasticsearch/kibana but am having issues matching out the figures between two _seemingly similar_ queries in the Kibana's Dev Tools SQL function and Kibana's visualization tool. The figures are extremely similar but not the same as I would expect. Kibana's visualization tools returns more counts for every FIELD bucket.

This is not unique to the query below but every query I have attempted.

Also, using pythons elasticsearch\_dsl package, I was able to match Kibana's SQL Dev Tools figures.

I am using Kibana V7.9.2.

**Example queries:**

**In SQL Dev Tools:**  
`POST _sql?format=txt { "query": "SELECT <FIELD>, COUNT(*) FROM <INDEX> WHERE DATETIME_FORMAT(\"@timestamp\", 'yyyy,MM,dd') = DATETIME_FORMAT(CAST('2021-05-10'AS DATE),'yyyy,MM,dd') GROUP BY <FIELD>" }`

**Kibana Visualization:**  
Start Date: May 10, 2021 @ 00:00:00.000  
End Date: May 10, 2021 @ 23:59:59.999  
Metric: Count  
Buckets:  
Split Rows  
Aggregation: Terms  
Field: FIELD  
Size: 99999

---

<div class="post-metadata">

### Author: ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [May 13, 2021, 9:33pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955/2 "2021-05-13T21:33:04Z")

</div>

reason is SQL query in kibana dev tool is putting out date as is.  
Kibana visualization is thinking your data is UTC and converting back to your browser time

This is problem with timezone. UTC to local time and vice-versa

---

<div class="post-metadata">

### Author: ![Christopher\_Mello](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christopher_mello/32/88736_2.png) [@Christopher\_Mello](https://discuss.elastic.co/u/Christopher_Mello)
#### Post date: [May 13, 2021, 9:36pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955/3 "2021-05-13T21:36:31Z")

</div>

Thank you so much @elasticforme, that makes complete sense. Is there a way to make these two queries return the same results in Kibana?

---

<div class="post-metadata">

### Author: ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [May 13, 2021, 9:42pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955/4 "2021-05-13T21:42:25Z")

</div>

yes you have to create another field in ELK which will take date with timezone and then ELK will not covert it.  
here is example

```
mutate{
         add_field => { "loged_date_timezone" => "%{loged_date}" }
   }
   ## this will be conerted to UTC by ELK
date {
      match => ["loged_date", "dd-MMM-yy HH:mm:ss", "ISO8601"]
      target => "loged_date"
   }

   ## lets set to UTC for all data, that way data will stay as it is in database (eventhought it is in CST time), and we can use this for sql query
   date { match => ["loged_date_timezone", "dd-MMM-yy HH:mm:ss", "ISO8601"]
        timezone => "Etc/UTC"
        target => "loged_date_timezone"
  }

```

now your index will have two date.  
you create two index pattern one with loged\_date  
another one with loged\_date\_timezone

on SQL use index pattern created with loged\_date\_timezone and date field loged\_date\_timezone  
on kibana viz use another one.

---

<div class="post-metadata">

### Author: ![Christopher\_Mello](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christopher_mello/32/88736_2.png) [@Christopher\_Mello](https://discuss.elastic.co/u/Christopher_Mello)
#### Post date: [May 13, 2021, 9:44pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955/5 "2021-05-13T21:44:57Z")

</div>

This is incredibly helpful @elasticforme. Thank you so much for the help

---

<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: [June 10, 2021, 9:45pm UTC](https://discuss.elastic.co/t/differences-in-values-between-dev-tools-sql-and-visualization/272955/6 "2021-06-10T21:45:33Z")

</div>

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