How to Search the Database by Date From Server-side JavaScript

Searching or querying a database is one of the most common actions in a mobile app. Appery.io platform provides a cloud database to store any app data and Server Code that allows to write any server-side app logic using JavaScript. Server Code has an API to query the database that makes it easy to perform search queries based on a date. In this blog post you will learn how to write a script to query a database collection using dates.

To start, we need to create a sample database collection. Our collection looks like this:

Database collection

The collection has two built-in date columns:

_createdAt – the time when a record was created
_updatedAt – the time when a record was updated (default value is set to _createdAt)

and two custom columns, both are of Date type:

startTime
endTime

You can of course include any other data.

Now let’s get to the code.

The first code examples shows how to query by using the built-in fields (_createdAt or _updatedAt).

var dbApiKey = "database API key";
var collection = "Goods";
var d = new Date();

var params = {};
params.criteria = {
   "_updatedAt": {
      "$lt": {$date: d.toISOString()} 
   }
};
var result = Collection.query(dbApiKey, collection, params);

This search query will return all objects where the _updatedAt date is less then ($lt) the current time (when the script runs).

When using one of the built-in data types in a query (_createdAt, _updatedAt), the date value must be formatted in ISO format. When using a custom column with Date type, using ISO format is not required.

The next code examples uses a custom endDate column (type Date). Note that the syntax to use the built-in date column (above) and a custom column is slightly different:

var dbApiKey = "database API key";
var collection = "Goods";

var params = {};
params.criteria = {
   "endTime": {
      "$lt": "2016-12-31 00:00:00.000"
   }
};
var result = Collection.query(dbApiKey, collection, params);

This query returns all records where the endTime is less than December 31, 2016. Based on data in the collection, only the record that contains tablets will be returned. This is how the response looks:

[{
    "product": "tablets",
    "_createdAt": {
       "$date": "2017-01-18T20:23:31.652Z"
    },
    "startTime": {
       "$date": "2016-12-01T00:00:00.000Z"
    },
    "_id": "587fcec3e4b07690b0037f12",
    "endTime": {
       "$date": "2016-12-21T00:00:00.000Z"
    },
    "_updatedAt": {
       "$date": "2017-01-18T20:56:26.322Z"
    }
}]

Let’s now look at another code example where we will use the startTime and endTime together.

In the following code snippet we query for all objects where the startTime is greater than December 31, 2016 and the endTime is less then February 1, 2017.

var dbApiKey = "database API key";
var collection = "Goods";

var params = {};
params.criteria = {
 "$and": [{
       "startTime": {
          "$gt": "2016-12-31 00:00:00.000"
       }
    }, {
       "endTime": {
          "$lt": "2017-02-01 00:00:00.000"
       }
    }]
};
var result = Collection.query(dbApiKey, collection, params);

Querying the database by dates or any other columns is best suited for server-side logic as you can test the script and ensure it works before using in an app. Moving this logic to the client would only complicate the app and make it more error prone.

Once the Server Code script is tested and read you can import the service API for it inside the App Builder. This short video shows how to import a backend service. To learn more about Server Code, watch the Server Code YouTube playlist videos.

Blog