Skip to content

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

NSDateFormatter *dateFormatter = [NSDateFormatter new];
dateFormatter.dateFormat = @"yyyyMMdd";
NSString *dateString = [dateFormatter stringFromDate:[[NSDate date] dateByAddingTimeInterval: -30 * 24 *60 * 60]];

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:[NSString stringWithFormat:@"created > '%@'", dateString]];

DataStoreFactory *dataStore = [Backendless.shared.data of:[Contact class]];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"Found objects: %@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let dateFormatter = DateFormatter()
dateFormatter.dateFormat = "yyyyMMdd"
let dateString = dateFormatter.string(from: (NSDate() as Date).addingTimeInterval(-30 * 24 * 60 * 60))

let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: String(format: "created > '%@'", dateString))

let dataStore = Backendless.shared.data.of(Contact.self)
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("Found objects: \(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

NSDateFormatter *dateFormatter = [NSDateFormatter new];
dateFormatter.dateFormat = @"yyyyMMdd";
NSString *dateString = [dateFormatter stringFromDate:[[NSDate date] dateByAddingTimeInterval: -30 * 24 *60 * 60]];

DataQueryBuilder *queryBuilder = [DataQueryBuilder new];
[queryBuilder setWhereClauseWithWhereClause:[NSString stringWithFormat:@"created > '%@'", dateString]];

MapDrivenDataStore *dataStore = [Backendless.shared.data ofTable:@"Contact"];
[dataStore findWithQueryBuilder:queryBuilder responseHandler:^(NSArray *foundObjects) {
    NSLog(@"Found objects: %@", foundObjects);
} errorHandler:^(Fault *fault) {
    NSLog(@"Error: %@", fault.message);
}];
let dateFormatter = DateFormatter()
dateFormatter.dateFormat = "yyyyMMdd"
let dateString = dateFormatter.string(from: (NSDate() as Date).addingTimeInterval(-30 * 24 * 60 * 60))

let queryBuilder = DataQueryBuilder()
queryBuilder.setWhereClause(whereClause: String(format: "created > '%@'", dateString))

let dataStore = Backendless.shared.data.ofTable("Contact")
dataStore.find(queryBuilder: queryBuilder, responseHandler: { foundObjects in
    print("Found objects: \(foundObjects)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})