How To Use Database Functions For Property Extraction And Conversion

Backendless version 5.7.3 released this week including several new features. In this article, we will look at how to use the new database functions to retrieve data derived from the information stored in your database.

For example, using these database functions, you can extract the date, month, or year from your DATETIME values, convert STRING literals to upper/lower case, etc.

One of the features in v.5.7.3 is the ability to retrieve dynamic properties from the Backendess database. The feature relies on a set of functions supported by Backendless that perform some kind of data extraction or data conversion. Consider the following sample data:

Suppose the data for the name column is needed in the upper case. This can be done using the UPPER() function along with the dynamic property retrieval:

REST request:

/data/Person?property=UPPER(name) as upperCaseName

SDKs:

DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.AddProperty( "UPPER(name) as upperCaseName" );
Backendless.Data.of( "Person" ).find( queryBuilder )

For this request, the server returns the following response:

[
  {
    "upperCaseName": "AMBER",
    "objectId": "472289B3-A7EA-EF20-FFEA-86D6A4457D00",
    "___class": "Person"
  },
  {
    "upperCaseName": "JEFF",
    "objectId": "781C695D-445B-53E0-FFC0-8EC66221DC00",
    "___class": "Person"
  },
  {
    "upperCaseName": "BOB",
    "objectId": "A8EE8F78-EE57-BB9C-FF69-63ECA99E2200",
    "___class": "Person"
  },
  { ... },
  { ... },
  { ... },
  { ... }
]

As you can see, there is now the upperCaseName property in the response. Notice the values of the property are returned in the upper case. This is what the UPPER() function does. Below you will find a list of functions Backendless supports in the retrieving dynamic property feature:

DATETIME Database Functions

• DATE( dateTimeColumnName ) – extracts the date part of a DATETIME column value.
• TIME( dateTimeColumnName ) – extracts the time portion of a DATETIME column value.
• WEEK( dateTimeColumnName ) – returns the week number of a DATETIME column value
• WEEK( 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( 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( dateTimeColumnName ) – returns the quarter of the year for a DATETIME column value, in the range 1 to 4.
• YEAR( dateTimeColumnName ) – returns the year for a DATETIME column value, in the range 1000 to 9999, or 0 for the “zero” date.
• DAYOFMONTH( 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( dateTimeColumnName ) – returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday)
• DAYOFYEAR( dateTimeColumnName ) – returns the day of the year (1-366)
• HOUR( dateTimeColumnName ) – returns the hour for time. The range of the return value is 0 to 23 for time-of-day values.
• MINUTE( dateTimeColumnName ) – returns the minute for time, in the range 0 to 59
• SECOND( dateTimeColumnName ) – returns the second for time, in the range 0 to 59.

STRING Database Functions

• TRIM( stringORTextColumnName )– returns a string value from the STRING or 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.

The functions can be combined. For example, the following expression converts the column value to upper case and then reverses the character order:

REVERSE( UPPER( columnName ) )

When requesting a dynamic property from Backendless, keep in mind you can assign the name of the property using the following format:

REVERSE( UPPER( columnName ) ) as reversedUpperCaseName

Where reversedUpperCaseName becomes the property name which will contain the calculated value in the response.

That’s all folks! Check out the other new features we introduced in v5.7.3:

• Introducing The Generate Columns Feature
• Using Smart Text To Personalize Push Notification Images
• How To Include And Exclude Properties From A Response

Happy coding!