In an Azure Functions binding, is there any way to query Cosmos DB for documents based on a date relative to today?

Question

I've got an Azure Function with a Cosmos DB binding. What I want to do is to get all the documents with a date in the future.

Something like this would work...

SqlQuery = "SELECT * FROM c WHERE c.Filter = {FilterTerm} AND c.SomeDate < '" + DateTime.UtcNow + "'")]

Except that the fact that the binding live in an attribute means they have to be a constant expression.

As far as I can tell, there aren't any functions built into Cosmos DB (like, for instance, GetDate() in T-SQL).

So currently I'm querying the lot and doing the date filtering in memory. This works, but it means a lot more DB traffic than I'd like.

Is there any way to do this more effectively?

Hi,Tom.Not sure what you said that the binding live in an attribute means they have to be a constant expression. Per my understanding, you want to implement GetDate() function in cosmos db query sql, right? So that you could filter the data on the server side. — Jay Gong
@JayGong I think we're talking about the same thing. Your answer looks promising - I'll give it a whirl. — Tom Wright

Jay Gong Jay Gong · Accepted Answer · 2018-09-06T02:56:19

Though there aren't any functions like GetDate() built into Cosmos DB cosmos db, you could use UDF to implement similar function.

udf:

function convertTime(unix_timestamp){

      var date = new Date();
      return date;
    //   var year = date.getFullYear();
    //   var month = ("0"+(date.getMonth()+1)).substr(-2);
    //   var day = ("0"+date.getDate()).substr(-2);
    //   var hour = ("0"+date.getHours()).substr(-2);
    //   var minutes = ("0"+date.getMinutes()).substr(-2);
    //   var seconds = ("0"+date.getSeconds()).substr(-2);

    //   return year+"-"+month+"-"+day+" "+hour+":"+minutes+":"+seconds;
}

You could convert any format you want to match your field.

sql:

SELECT c.SomeDate < udf.nowDate() FROM c

Any concern, please feel free to let me know.

In an Azure Functions binding, is there any way to query Cosmos DB for documents based on a date relative to today?

1 Answers