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"}
} ]
}
}