search

NLPchina / elasticsearch-sql

Use SQL to query Elasticsearch
Apache License 2.0
6.99k stars 1.54k forks source link

datediff function in sql plugin #147

Open satapsa opened 8 years ago

satapsa commented 8 years ago

Is there a datediff function in sql plug in, which can provide the number of days between 2 dates ?

eliranmoyal commented 8 years ago

look at the last example here: https://github.com/NLPchina/elasticsearch-sql/wiki/Script-Fields

satapsa commented 8 years ago

Hi Eliranmoyal

I want the difference between the min and the max of the test date. In the example it is dealing with the the date field directly

When I rin the queru separately I am getting a min - "minDate":{"value":1.4416704E12,"value_as_string":"2015-09-08 00:00:00"}}} and max - "maxDate":{"value":1.4418432E12,"value_as_string":"2015-09-10 00:00:00"

So the date difference should be 2

However am not getting the result in the query below.

SELECT min(test_date) as minDate,max(test_date) as maxDate, script('diffInDays','(doc[\'minDate\'].value - doc[\'maxDate\'].value)/3600/24*10000000000) FROM xxx/test_locs

Is there a way around it ?

Thanks for your help in advance.