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

Important

Make sure to replace xxxx in the domain name in the sample requests below to the one assigned to your application.

Finding objects created after 12/16/2014:

curl "https://xxxx.backendless.app[`/data/Person?where=created>1418680800000`](/data/Person?where=created%3E1418680800000)"

where where=created>1418680800000 is the number of milliseconds since epoch or December, 12, 2014.

Alternatively, the query may have the following format:

curl "https://xxxx.backendless.app[`/data/Person?where=created>'12/16/2014'`](/data/Person?where=created%3E'12/16/2014')"