# Convert MySQL query to elasticsearch **URL:** \ **Category:** Kibana\ **Created:** [February 3, 2016, 7:32am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830 "2016-02-03T07:32:57Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 3, 2016, 7:32am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/1 "2016-02-03T07:32:57Z")
Help me to convert this MySQL query to elasticsearch (json) query and how to visualize it on kibana. ``` SELECT user, call_date, sum(length_in_sec) as 'work_time', (max(end_epoch)-min(start_epoch))-sum(length_in_sec) as 'idle_time' FROM vicidial_log WHERE date(call_date)='2016-01-12' group by user ``` ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 3, 2016, 1:22pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/2 "2016-02-03T13:22:34Z")
If you provide details on how you've structured data in Elasticsearch, it may be easier to help you. What type of visualization are you looking for... a table? In that case, your SELECT would map to metrics in the Y-axis, Date is just something you'd select using a time picker, and GROUP BY is a Terms Aggregation. ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 5, 2016, 6:15am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/4 "2016-02-05T06:15:35Z")
Dear tbragin, Thanks a lot for your response. This is the call\_log data collection of the tele-callers of our company. The mapping of my index is following: ``` "mappings": { "vicidial_log": { "_timestamp": { "enabled": true }, "properties": { "call_date": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }, "end_epoch": { "type": "integer" }, "lead_id": { "type": "integer" }, "length_in_sec": { "type": "integer" }, "phone_number": { "type": "string", "analyzer": "standard" }, "start_epoch": { "type": "integer" }, "log_id": { "type": "string", "analyzer": "standard" }, "user": { "type": "string", "analyzer": "standard" } } } } ``` I have attached the sample data, and expected visualization hear. Is this understandable? or you want more details, I'm ready to give you. regds, croos. ![](https://us1.discourse-cdn.com/elastic/original/2X/c/cc939549307f92d5e0c81fbaee650a0ec5b22cd5.JPG) ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 8, 2016, 6:58pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/5 "2016-02-08T18:58:56Z")
The combination of bar chart and data table visualizations should work for you. What have you tried so far? ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 8, 2016, 7:45pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/6 "2016-02-08T19:45:29Z")
Only the bar chart. The table is just for show my sample data to you. ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 8, 2016, 8:58pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/7 "2016-02-08T20:58:38Z")
I think I understand the reason for your question now - you are not indexing "idle time" and "talk time" directly in ES. You should be able to calculate the "idle time" and "talk time" as [scripted fields in Kibana](https://www.elastic.co/guide/en/kibana/current/managing-fields.html#create-scripted-field), assuming you can do that based on data from a single document. If your calculation needs to go across document, you might need to use a script as an input to Date Histogram (see example [here](https://discuss.elastic.co/t/display-concurrency-in-data-on-kibana/26006)). Hope this helps! ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 9, 2016, 5:56am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/8 "2016-02-09T05:56:18Z")
Thanks a lot Bragin, You got my question. Now I understand the [scripted field](https://www.elastic.co/guide/en/kibana/current/managing-fields.html#create-scripted-field) based on data from a single document and had a idea about Date Histogram. As you mentioned, My case is need to go across document. Now my question is how to get the `maximum` and `minimum` value of a field from across document? Would you please explain me a little bit detail. ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 9, 2016, 10:18am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/9 "2016-02-09T10:18:23Z")
Now I partially complete the bar chart that represent the sum of call length Vs user and call date. How to apply the scripting function for visualize an additional bar parallel with existing bar to represent the Idle time? > hint: > Idle time of an employee in particular date is calculate by the following equation, > idle\_time=(max(end\_epoch) - min(start\_epoch)) - sum(length\_in\_sec) > max(end\_epoch), min(start\_epoch) are have to calculate for each and every day. ![](https://us1.discourse-cdn.com/elastic/original/2X/c/c488ca7821d674cf3c61edbcd24f25df5e60ffcd.JPG) ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 9, 2016, 5:24pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/10 "2016-02-09T17:24:08Z")
Try using "grouped" in the Options? ![](https://us1.discourse-cdn.com/elastic/original/2X/4/49f3eec7e6831a40de89555bfdbf69d7b2c8e29b.png) ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 9, 2016, 9:50pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/11 "2016-02-09T21:50:14Z")
ok. I got it. Would you please explain my previous question.. > [@Convert MySQL query to elasticsearch](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/8): > > Thanks a lot Bragin, You got my question. Now I understand the [scripted field](https://www.elastic.co/guide/en/kibana/current/managing-fields.html#create-scripted-field) based on data from a single document and had a idea about Date Histogram. As you mentioned, My case is need to go across document. Now my question is how to get the maximum and minimum value of a field from across document? Would you please explain me a little bit detail. and How to script the aggregation function for Y axis? ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 9, 2016, 11:00pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/12 "2016-02-09T23:00:26Z")
Yeah, there is an example of it here: [Display concurrency in data on Kibana](https://discuss.elastic.co/t/display-concurrency-in-data-on-kibana/26006) I'm happy to answer a specific question if you can tell me what you already tried based on your reading of that thread? ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 10, 2016, 7:15am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/13 "2016-02-10T07:15:19Z")
Hi, I uploaded the current problem in my task. ![](https://us1.discourse-cdn.com/elastic/original/2X/a/a1a5056c62a91aaf598b0b82a25ebaff44c7b1f1.png) And as I mentioned in earlier, the Minimum and Maximum values are have to get from across the field. Then as mentioned in the image, want to display the idle\_time like this. ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 10, 2016, 7:41am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/14 "2016-02-10T07:41:49Z")
I referred and understand the [aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-max-aggregation.html) in elasticsearch also. But still have no idea about the script for the above task in kibana. ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 10, 2016, 10:46pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/15 "2016-02-10T22:46:33Z")
You may need to do something more complicated, such as pass a [script to the date aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/2.2/search-aggregations-bucket-datehistogram-aggregation.html#_scripts) in order to do some calculations across the returned buckets. Notice in the example I linked to in the [thread](https://discuss.elastic.co/t/display-concurrency-in-data-on-kibana/26006/15) above, the script actually goes through values returned in the buckets and calculates an array of durations, returning that as a result: ```auto start = doc['recordStart'].value; duration = doc['recordDuration'].value; l = []; for (long i = 0; i < duration; i += 60000) { l.add(start + i); }; return l; ``` ---
**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\ **Post date:** [February 12, 2016, 10:43am UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/16 "2016-02-12T10:43:26Z")
Some how I got my first visualization of my task. Still have some issue in scripting. As you mentions, I'll have a deep look on that thread and catch you soon. Anyway thanks for your support. Regards, Croos. ![](https://us1.discourse-cdn.com/elastic/original/2X/3/3e37d84bbf038af68d033c96da98942502d06952.JPG) ---
**Author:** ![tbragin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tbragin/32/45166_2.png) [@tbragin](https://discuss.elastic.co/u/tbragin)\ **Post date:** [February 12, 2016, 3:13pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/17 "2016-02-12T15:13:03Z")
Sweet! That looks awesome! 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:02pm UTC](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830/18 "2017-07-06T14:02:19Z")