Database Functions¶
Backendless Database supports a variety of functions that can perform database data transformations, used for conditions and retrieval of custom properties. Functions can be used in three places:
Consider the following Person
data table:
Suppose we need to retrieve records from this table and perform the following:
- Calculate each person's age.
- Convert the name to upper case.
Using functions these tasks can be performed by Backendless instead your program. Below is an example of applying functions to achieve the result. Notice the values in the response:
This example uses the following functions:
UPPER(name)
- converts the value in thename
column to upper caseYEAR(NOW())-YEAR(dateOfBirth)
- calculates the difference between the current year and the year in thedateOfBirth
column.
Below is an example of using these functions in a Codeless block to retrieve database records:
As you case see, functions can be a powerful instrument to offload some data conversions and calculations to the database. Below is a list of functions supported by Backendless:
DATETIME Functions¶
These functions apply to database columns and values of the DATETIME type.
NOW()
- returns current date/time. The returned value can be used in all functions listed below. Additionally, you can pass the optionalfsp
( fractional seconds precision ) argument to the function, that will return a value in the following format:'YYYY-MM-DD HH:MM:SS.FFFFFF’
or'2020-06-23 11:19:06.095048'
. Note, that thefsp
argument returns up to 6 digits of microseconds precision.UNIXTIMESTAMP( date )
- returns the number of seconds that has passed since1970-01-01 00:00:00 UTC
as an unsigned integer. When thedate
argument is passed to the function, it returns the value of the argument as seconds passed since1970-01-01 00:00:00 UTC
.
Thedate
argument can be any of the following:
- Name of a database column of theDATETIME
type;
- A function that returns a date (DATE()
,NOW()
,DATEADD()
,DATESUB()
);
- Literal date value as a string expressed in any of the supported formats;DATE( dateValue or dateTimeColumnName )
- extracts the date part of aDATETIME
column value.TIME( dateValue or dateTimeColumnName )
- extracts the time portion of aDATETIME
column value.WEEK( dateValue or dateTimeColumnName )
- returns the week number of aDATETIME
column valueWEEK( dateValue or dateTimeColumnName, mode )
- same as above, but allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. Second argumentmode
== 0 ~ 7. Themode
argument is:
mode |
First day of week |
Range |
Week 1 is the first week... |
---|---|---|---|
0 |
Sunday |
0-53 |
with a Sunday in this year |
1 |
Monday |
0-53 |
with 4 or more days this year |
2 |
Sunday |
1-53 |
with a Sunday in this year |
3 |
Monday |
1-53 |
with 4 or more days this year |
4 |
Sunday |
0-53 |
with 4 or more days this year |
5 |
Monday |
0-53 |
with a Monday in this year |
6 |
Sunday |
1-53 |
with 4 or more days this year |
7 |
Monday |
1-53 |
with a Monday in this year |
MONTH( dateValue or dateTimeColumnName )
- returns the month for aDATETIME
column value, in the range 1 to 12 for January to December, or 0 for dates such as ‘0000-00-00’.QUARTER( dateValue or dateTimeColumnName )
- returns the quarter of the year for aDATETIME
column value, in the range 1 to 4.YEAR( dateValue or dateTimeColumnName )
- returns the year for aDATETIME
column value, in the range 1000 to 9999, or 0 for the “zero” date.DAYOFMONTH( dateValue or dateTimeColumnName )
- returns the day of the month for aDATETIME
column value, in the range 1 to 31, or 0 for dates such as ‘0000-00-00’.DAYOFWEEK( dateValue or dateTimeColumnName )
- returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday)DAYOFYEAR( dateValue or dateTimeColumnName )
- returns the day of the year (1-366)HOUR( dateValue or dateTimeColumnName )
- returns the hour for time. The range of the return value is 0 to 23 for time-of-day values.MINUTE( dateValue or dateTimeColumnName )
- returns the minute for time, in the range 0 to 59SECOND( dateValue or dateTimeColumnName )
- returns the second for time, in the range 0 to 59.DATESUB( dateValue or dateTimeColumnName, intervalValue, intervalName )
DATEADD( date, intervalValue, intervalName )
perform arithmetic functions with date values.DATESUB
subtractsintervalValue
fromdate
.DATEADD
addsintervalValue
todate
.
TheintervalValue
andintervalName
arguments must correspond with the values and samples shown below:
intervalName Value |
Value Format |
Sample Value |
---|---|---|
'MICROSECOND' |
MICROSECONDS (as a number) |
55 |
'SECOND' |
SECONDS (as a number) |
32 |
'MINUTE' |
MINUTES (as a number) |
41 |
'HOUR' |
HOURS (as a number) |
2 |
'DAY' |
DAYS (as a number) |
3 |
'WEEK' |
WEEKS (as a number) |
2 |
'MONTH' |
MONTHS (as a number) |
1 |
'QUARTER' |
QUARTERS (as a number) |
2 |
'YEAR' |
YEARS (as a number) |
1 |
'SECOND_MICROSECOND' |
'SECOND.MICROSECOND' |
'5.23434' |
'MINUTE_MICROSECOND' |
'MINUTES:SECONDS.MICROSECONDS' |
'23:12.65243' |
'MINUTE_SECOND' |
'MINUTES:SECONDS' |
'12:45' |
'HOUR_MICROSECOND' |
'HOURS:MINUTES:SECONDS.MICROSECONDS' |
'5:23:34.32443' |
'HOUR_SECOND' |
'HOURS:MINUTES:SECONDS' |
'2:45:12' |
'HOUR_MINUTE' |
'HOURS:MINUTES' |
'5:30' |
'DAY_MICROSECOND' |
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
'3 3:45:05.234234' |
'DAY_SECOND' |
'DAYS HOURS:MINUTES:SECONDS' |
'7 3:37:55' |
'DAY_MINUTE' |
'DAYS HOURS:MINUTES' |
'8 12:33' |
'DAY_HOUR' |
'DAYS HOURS' |
'3 3' |
'YEAR_MONTH' |
'YEARS-MONTHS' |
'10 1' |
STRING Functions¶
These functions apply to database columns and value of the STRING
and TEXT
type.
TRIM( stringORTextColumnName )
- returns a string value from theSTRING
andTEXT
column with leading and trailing spaces removed.REVERSE( stringORTextColumnName )
- returns a string value with the characters positioned in the reversed order.UPPER( stringORTextColumnName )
- returns a string value with all alphabetical characters converted to the upper case equivalent.LOWER( stringORTextColumnName )
- returns a string value with all alphabetical characters converted to the lower case equivalent.FINDINSET( stringColumn or stringLiteral, stringColumn or stringLiteral )
- finds and returns the position of the value represented by the first argument in a set identified by the second argument. The second argument must be a comma-separated list of strings to be searched. If the second argument is a column name, the search occurs in the values represented by the column. The function returns:
- zero if the first argument value is not found in the set identified by the second argument or if the second argument value is an empty string;
- a positive integer if the first argument value is found in the second argument's list of strings. The integer is the position in the list.
-NULL
if either argument isNULL
.
Miscellaneous Functions¶
IF( condition, true_value, false_value )
- function allows validating a specific condition. Whencondition
evaluates totrue
, thentrue_value
is returned. Otherwise, thefalse_value
is returned. Function arguments can be string, integer or column names. Returned values can be integer or string.
WHERE, GROUP BY clauses can be used to make a query.
Example 1:
curl -X GET 'https://eu-api.backendless.com/:appId/:restKey/data/Person?where=IF(age>18, current_city, birth_city)='New York''
Example 2:
curl -X GET 'https://eu-api.backendless.com/:appId/:restKey/data/Person?where=IF(age>18, current_city, birth_city)='New York'&property=IF(age>18, 'adult', 'child') as maturity&having=IF(oldIdentityField is null, newIdentityField, oldIdentityField)=1'
ISNULL( columnName )
- returns 1 if the value in the referenced column is NULL
, otherwise returns 0.
* ISNOTNULL( columnName )
- returns 1 if the value in the referenced column contains a non-NULL
value otherwise returns 0.
* COALESCE( value1, value2, value3, valueN )
- function returns the first non-NULL
value of a list, or NULL
if there are no non-NULL
value.
Function arguments can be literal values or column names. When the argument values are column names, the function will return the first non-NULL value from the referenced columns. Consider the following data table. Notice that some records do not have values for the email
and phoneNumber
columns:
Below is a result of using the COALESCE function to retrieve a non-NULL value from email
and phoneNumber
columns. As you can see, the function returned:
- NULL
for Bob
- a value from the phoneNumber
column for Anna
- a value from the email
column for Joe