Using Dates in Search

There is a special consideration for the whereClause-based queries which reference a column of the DATETIME data type. Typically a DATETIME column is referenced in a comparison against a scalar value describing a specific date or a timestamp. The scalar value can be a number of milliseconds since the epoch (UNIX timestamp as milliseconds) or a string. Backendless supports a variety of date formats for the latter. For example, the queries below will find all the objects which were updated after March 23rd, 2015:

updated > '23-Mar-2015'

updated > '03/23/2015'

updated > 1427068800000

Comparison Operators

Backendless supports the following date comparison operators:

Column's value is after the specified date/time: use either > or the after keyword:

birthDate > '22-Dec-1980'

birthDate after 1427068800000

Column's value is before the specified date/time: use either < or the before keyword:

birthDate < '22-Dec-1980'

birthDate before 1427068800000

Column's value is either at or after the specified date/time: use either => or the at or after keyword:

birthDate >= '28-10-1988'

birthDate at or after '10/28/1988 00:00:00 GMT-0200'

Column's value is either at or before the specified date/time: use either <= or the at or before keyword:

birthDate >= '28-10-1988'

birthDate at or after '10/28/1988 00:00:00 GMT-0200'

Note: the whereClause-based queries can be tested in the Backendless Console with the SQL Search turned on.

Supported Date Formats

Date/time string values may be in any of the following formats. The pattern letters have the same definition as in Java's SimpleDateFormat:

EEE MMM dd HH:mm:ss zzz yyyy
yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
MM/dd/yyyy HH:mm:ss 'GMT'z
MM.dd.yyyy HH:mm:ss 'GMT'z
MM-dd-yyyy HH:mm:ss 'GMT'z
MM/dd/yyyy HH:mm:ss z
MM.dd.yyyy HH:mm:ss z
MM.dd.yyyy HH:mm:ss
MM-dd-yyyy HH:mm:ss
MM/dd/yyyy HH:mm:ss
MM.dd.yyyy
MM-dd-yyyy
MM/dd/yyyy HH:mm:ss 'GMT'Z
MM/dd/yyyy HH:mm
MM/dd/yyyy
dd/MMM/yyyy
dd-MMM-yyyy
EEEEE, d MMMMM yyyy
yyyy/MM/d/HH:mm:ss
yyyy-MM-dd'T'HH:mm:ss
EEEEE, MMMMM d, yyyy
MMMMM d, yyyy
yyyy M d
yyyyMMMd
yyyy-MMM-d
yyyy-M-d, E
'Date' yyyy-MM-dd
yyyy-MM-dd'T'HH:mm:ssZ
yyyy-MM-dd'T'HH:mmZ
yyyy-MM-dd
yyyy-'W'w
yyyy-DDD
d MMMMM yyyy, HH'h' mm'm' ss's'

Using Functions

Backendless supports a variety of functions that work with the date/time values. These functions can be used to perform some kind of transformation of a value in a DATETIME column and use the result in a where clause condition. Consider the following examples:

A condition to retrieve database objects where the saleDate column contains a date that is in the second quarter:

QUARTER( saleDate ) = 2

A condition to retrieve database objects where the dateOfBirth column contains dates that older than 18 years:

YEAR(DATEADD( dateOfBirth, '18 0', 'YEAR_MONTH')) < YEAR(NOW())

Example

Using DictionaryCustom Class

String whereClause = "updated after " + DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond;
DataQueryBuilder queryBuilder = DataQueryBuilder.Create();
queryBuilder.SetWhereClause( whereClause );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Dictionary<string, object>> result = Backendless.Data.Of( "Contact" ).Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Dictionary<string, object>>> findCallback;
findCallback = new AsyncCallback<IList<Dictionary<string, object>>>(
  foundObjects =>
  {
    System.Console.WriteLine( "Server returned " + foundObjects.Count + " objects" );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of( "Contact" ).Find( queryBuilder, findCallback );

String whereClause = "updated after " + DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond;
DataQueryBuilder queryBuilder = new DataQueryBuilder.Create();
queryBuilder.SetWhereClause( whereClause );

// ***********************************************************
// Blocking API:
// ***********************************************************
IList<Contact> result = Backendless.Data.Of<Contact>().Find( queryBuilder );

// ***********************************************************
// Non-blocking API:
// ***********************************************************
AsyncCallback<IList<Contact>> findCallback;
findCallback = new AsyncCallback<IList<Contact>>(
  foundObjects =>
  {
    System.Console.WriteLine( "Server returned " + foundObjects.Count + " objects" );
  },
  error =>
  {
    System.Console.WriteLine( "Server returned an error " + error.Message );
  } );

Backendless.Data.Of<Contact>().Find( queryBuilder, findCallback );

Codeless Reference

Consider the following data table called Person:

As evident from the data table above, each object stored within it includes the creation date. The values stored in the "created" column correspond to the DATETIME data type.

Suppose you need to retrieve objects that were created after May 11, 2023. To accomplish this, you need to set the condition in the "where clause" property as "created > 1683838706000". Here, the "created" represents the column where the objects' creation dates are stored, and the number in the expression corresponds to the number of milliseconds elapsed since the Unix Epoch. Note that the Unix Epoch represents the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970

Important

For a detailed description of all input parameters see the Basic Object Retrieval topic of this guide.

After the Codeless Logic runs, the operation returns 4 objects from the data table that were created after May 11, 2023: