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:

• Generated columns
• Where Clause
• Dynamic Properties

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 the name column to upper case
• YEAR(NOW())-YEAR(dateOfBirth) - calculates the difference between the current year and the year in the dateOfBirth 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 optional fsp ( 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 the fsp argument returns up to 6 digits of microseconds precision.
• UNIXTIMESTAMP( date ) - returns the number of seconds that has passed since 1970-01-01 00:00:00 UTC as an unsigned integer. When the date argument is passed to the function, it returns the value of the argument as seconds passed since 1970-01-01 00:00:00 UTC.
  The date argument can be any of the following:
  - Name of a database column of the DATETIME 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 a DATETIME column value.
• TIME( dateValue or dateTimeColumnName ) - extracts the time portion of a DATETIME column value.
• WEEK( dateValue or dateTimeColumnName ) - returns the week number of a DATETIME column value
• WEEK( 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 argument mode == 0 ~ 7.  The mode 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 a DATETIME 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 a DATETIME column value, in the range 1 to 4.
• YEAR( dateValue or dateTimeColumnName ) - returns the year for a DATETIME 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 a DATETIME 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 59
• SECOND( 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 subtracts intervalValue from date. DATEADD adds intervalValue to date.
  
  The intervalValue and intervalName 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 the STRING and TEXT 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 is NULL.

Miscellaneous Functions

• IF( condition, true_value, false_value ) - function allows validating a specific condition. When condition evaluates to true, then true_valueis returned. Otherwise, the false_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 phoneNumbercolumn for Anna
- a value from the email column for Joe