How to Query a Database by Date

Selecting a date via a Datepicker component and querying a database by date is one of the most common tasks a mobile app will do. With Appery.io’s out-of-the-box cloud database and auto-generated APIs, querying the database is fast and simple.

One thing you need to make sure is that the date selected in the UI (Datepicker) matches the format used in the database. This post will show you how to write a short JavaScript function to format the date. When building a mobile app in Appery.io, you can write any custom code. This is a very powerful as it allows you to customize the app as much as you need.

1. The first step is to create a new JavaScript file via Create new > JavaScript with the following code:

var datepickerQuery = function(columnName, value){
   var today = new Date(value);
   var todayTimeOffset = today.getTimezoneOffset() / (-60);
   var todayLocalDate = new Date(today.getTime() + todayTimeOffset * 3600000);
   var todayDateText = todayLocalDate.toISOString().replace(/T.*/gi, "");
   var tomorrowLocalDate = new Date(today.getTime() + todayTimeOffset * 3600000 + 86400000);
   var tomorrowDateText = tomorrowLocalDate.toISOString().replace(/T.*/gi, "");

   if ((columnName === "_createdAt") || (columnName === "_updatedAt")) {
      return '{"$and":[{"'+columnName+'":{"$gte": {"$date":"'+todayDateText+'"}}}, {"'+columnName+'":{"$lt": {"$date": "'+
tomorrowDateText+'"}}}]}}';

   } else {
      return '{"$and":[{"'+columnName+'":{"$gte": "'+todayDateText+'"}}, {"'+columnName+'":{"$lt": "'+
         tomorrowDateText+'"}}]}}';
   }
};

2. Next you need to generate the API to query the database. That’s quickly done by selecting Create new > Database Services, selecting the database, then the collection and the query API.

3. Add the service to a page and setup a mapping. Notice that you are will be mapping from a Datepicker component to the service. This is how Before send event mapping looks:

Before invoking the service mapping

Before the service is invoked, you can run JavaScirpt to help you format the data as need. That’s exaclty what you are going to do with the help of the function created in step 1:

return datepickerQuery("columnName", value);

Where columnName – column name to be queried.

4. This is how Success event mapping looks:

After service invoked mapping

For example, in database we have four employees, whose projects were started on date in column start and completed on date in column finish. Records in database were created on date in predefined column _createdAt and updated – in predefined column _updatedAt:

Sample data in database

5. Now you can test the app. This is an example of getting records that were finished on June 3, 2015 (searching by finish column):

Searching by finish date

This is an example of getting records that were updated on June 24, 2015 (searching by _updatedAt column):

Searching by updated date

With out-of-the-box tools and a custom JavaScript function, querying the the database by date is quick and simple.

Don’t forget to check out our YouTube channel for training videos, webinar and more.

Blog