How to filter records on the basis of customized datetime stamp format in Aerospike


#1

I am saving records with datetime stamp in aerospike in the format : YYYY-MM-DDTHH:mm:ssZ e.g: 2015-03-31T11:24:22Z. I want to filter records based on requests like 4h, 1d etc. If I receive request for last 4h and suppose current date is 31 March 2015 and time is 11:24, then I need to filter records in a range from 2015-03-31T07:24:22Z to 2015-03-31T11:24:22Z. How is it possible through Aerospike filter? I know about filter range in NodeJS client i.e: aerospike.filter.range but I am not clear how to perfectly use it for my purpose. I had tried the following:

  1. I made a secondary key on datetime bin and tried the following query

    select * from test.myRecords where dateOfRecords between 2015-03-31T07:24:22Z and 2015-03-31T11:24:22Z I know it is not sensible but I want something in a way that not disturbed current format of datetime stamp and I fulfill my purpose with the same format.

2.Tried to save datetime stamp by giving as int and the query as suggested in How to store/retrieve date in Aerospike database?

Kindly help me, solution with NodeJS client will be highly appreciated but other solutions and suggestions are also welcomed. If aerospike is listening only one specific datetime stamp format so kindly inform me and working examples will be greatly helpful for me.


#2

First, it makes the most sense for you to also represent the datetime in a separate bin as a Unix-epoch integer value. That would allow you to build a numeric secondary index on the other bin and do efficient range queries on it.

If you insist on comparing two strings you can use a stream UDF combined with a query in order to get at those. I answered something similar on this stackoverflow question.