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'
Example¶
var whereClause = "updated after " + timestamp; var queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause( whereClause ); Backendless.Data.of( "Contact" ).find( queryBuilder ) .then( function( result ) { // result is an array of plain JS objects }) .catch( function( error ) { });
// define constructor function first: function Contact() { // define Contact properties here } var whereClause = "updated after " + timestamp; var queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause( whereClause ); Backendless.Data.of( Contact ).find( queryBuilder ) .then( function( result ) { // result is an array of Contact objects }) .catch( function( error ) { });