how to create query which can calculate time difference?

Question

I have and index with following documents:

{name: 'Device1', type: 'start', 'eventTime': '2013-02-19 12:00:00'}
{name: 'Device2', type: 'start', 'eventTime': '2013-02-19 12:02:00'}
{name: 'Device1', type: 'stop', 'eventTime': '2013-02-19 12:45:00'}
{name: 'Device2', type: 'stop', 'eventTime': '2013-02-19 12:50:00'}

I would like to create a query which will return for me a new field with time differences between eventTime's, faceted by device name and regards with field type. For the example it should be:

{name: 'Device1', 'type': 'it really doesnt matter', eventTime: 'also doesnt matter', duration: '00:45:00'}
{name 'Device2', 'type': 'it really doesnt matter', eventTime: 'also doesnt matter', duration: '00:48:00'}

Is it possible with elastic search query language?

Answer 1

I don't believe this is possible with the way you have each document currently. If you store the documents as:

{name: 'Device1', startTime: '2013-02-19 12:00:00', endTime: '2013-02-19 12:45:00'}
{name: 'Device2', startTime: '2013-02-19 12:02:00', endTime: '2013-02-19 12:50:00'}

You could then return a script field which is the time difference.

{    
  "query" : {
    ...
  },
  "script_fields" : {
    "timedifference" : {
      "script" : "doc['endTime'].value - doc['startTime'].value"
    }
  }
}