Full Text Search of Indexed TIMESTAMP Scalar

Suppose you start a store named kvstore and create the tsTable with the same timestamp values as those presented previously, where each such value was stored in the table with precision 6. After registering the store with your Elasticsearch cluster (running on a host named eshost), a Text Index named tsIndex on the table's ts field is created by executing the following command from the Admin CLI:

kv-> execute 'CREATE FULLTEXT INDEX tsIndex ON tsTable (ts{"type":"date"})';

Executing queries such as the following can then be used to perform a Full Text Search on the data that was indexed:

List all values, sorted in ascending order

curl –X GET 'http://eshost:9200/ondb.kvstore.tstable.tsindex/_search?pretty' 
                  '-d {"sort":[{"ts":"asc"}]}'

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 3,
    "successful" : 3, 
    "failed" : 0
  },
  "hits" : {
    "total" : 5,
    "max_score" : null,

    "hits" : [ {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0000",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"0"},  
                          "ts":"1996-12-31T23:01:43.987654"},

      "sort" : [852073303123]
    
}, {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0002",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"2"},  
                          "ts":"1998-10-26T08:33:59.735978"},

      "sort" : [909435821111]
    
}, {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0003",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"3"},  
                          "ts":"2001-09-15T23:01:43.555667"},

      "sort" : [995911599555]
  
  }, {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0004",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"4"},  
                          "ts":"2002-04-06T17:07:38.765346"},

      "sort" : [1024765658765]
    
}, {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0001",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"1"},  
                          "ts":"2005-03-20T14:10:25.258000"},

      "sort" : [1091264176173]
  
  } ]
  }
}

Perform an exact match to find a specific date and time

curl –X GET 'http://eshost:9200/ondb.kvstore.tstable.tsindex/_search?pretty' 
                  '-d {"query":{"term":{"ts":"2005-03-20T14:10:25.258000"}}}' 

{
  ....
  "hits" : {
    "total" : 1,
    "max_score" : null,

    "hits" : [ {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0001",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"1"},  
                          "ts":" 2005-03-20T14:10:25.258000},
  
  } ]
  }
}

Find dates that fall within a specific range of dates and times

curl –X GET 'http://eshost:9200/ondb.kvstore.tstable.tsindex/_search?pretty' 
                  '-d {"query":{"range":{"ts":{"gte":" 1998-10-26T08:33:59.735978","lt":" 2002-04-06T17:07:38.9"}}}}' 

{
  ....
  "hits" : {
    "total" : 3,
    "max_score" : null,

    "hits" : [ {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0004",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"4"},  
                          "ts":"2002-04-06T17:07:38.765346},
    
}, {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0002",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"2"},  
                          "ts":"1998-10-26T08:33:59.735978"},

      "sort" : [909435821111]
    
}, {
      "_index" : "ondb.kvstore.tstable.ts",
      "_type" : "text_index_mapping",
      "_id" : "/w/0003",
      "_score" : null,

      "_source":{"_pkey":{"_table":"tstable","id":"3"},  
                          "ts":"2001-09-15T23:01:43.555667"}
  
  } ]
  }
}