Using functions and formulas in WHERE Clause

REST
1

Hi there, I did a search on the forum but can’t seem to find anything related to this…

Would like to know if it’s possible to use formulas and functions in the WHERE clause when using Backendless’s REST API?

For example, I have a table of classes and there’s a DATETIME field “classStart”.

I’d like to retrieve only the classes with classStart between the current time and tomorrow as a list, and the classes with classStart between the start of tomorrow and 2 days later, and so on.

I’m not sure if it’s possible and what syntax should I use… Any help is greatly appreciated.

Thank you in advance,
Vance

2

Hi @Peculiar_Yogi

I can recommend you use the following syntax:

where=DATETIME_FUNCTION(classStart) > date_today AND DATETIME_FUNCTION(classStart) < date_tomorrow

Also, you can find documentation on working with dates and functions here:

Regards,
Inna

3

Thanks @Inna_Shkolnaya ! How about if I want…

where=DATETIME_FUNCTION(classStart) > date_tomorrow AND DATETIME_FUNCTION(classStart) < date_followingday

where=DATETIME_FUNCTION(classStart) > date_followingday AND DATETIME_FUNCTION(classStart) < date_dayafterfollowingday

…And so on?

I think there’s a syntax documentation for this or something, yes?

Thank you so much for your help, Inna, really appreciate it :slight_smile:

4

In this case, your code would be figuring out the specific values for date_tomorrow, date_followingday, date_dayafterfollowingday and then putting these values into the query. The syntax is the same as shown, it doesn’t change. What changes is the specific values for the range of dates you need to retrieve data for.

Regards,
Mark

5

Thanks @mark-piller

I’m guessing on the request URL, I’d have to include URL parameters that allow me to insert those specific values dynamically through formulas or something on the client side. This shouldn’t be done with formulas on the request URL from Backendless.

6

What formulas you are talking about ?
where=DATETIME_FUNCTION(classStart) > date_today AND DATETIME_FUNCTION(classStart) < date_tomorrow - some_value + another_value
Something like this ?

7

Hey @oleg-vyalyh, yes, that’s right. I’m thinking of performing mathematical functions such as adding numbers.

For eg. date_today + 86400000

Not sure if that’s possible?

Thanks very much

8

If you want to use fields with type DATETIME as milliseconds, just use the filed name.
So in your case:
where=classStart_in_ms > date_today_in_ms AND classStart_in_ms < date_tomorrow_in_ms - some_value + another_value

In other case, f.e. it works like this:

  • date(21000101) = ‘2100-01-01’
  • DATE(updated) > date(20200412)
  • TIME(234521) = ‘23:45:21’
    Other functions (week, month, dayofweek, etc.) return number.
9

Hey @oleg-vyalyh, I was thinking of using formulas that will take dynamic data. I’m guessing that’s not recommended.

Ideally, we would want to set a variable on the client side (eg. date_now) and have the client side do the dynamic formulas, yes?

In my case, I’m using Appgyver. But I realised there are some issues that Appgyver have to fix in terms of passing dynamic data/formulas. Just wanted to see if it’s possible to do the dynamic data passing directly in the where clause on Backendless as a workaround. Guess not…

Thanks anyway!

10

Hello @Peculiar_Yogi

take a look at this doc https://backendless.com/docs/rest/data_working_wih_properties.html#dynamic-properties
you can request transformed values, is this what you are looking for?

Regards, Vlad

11

Hey @vladimir-upirov, I was thinking of there’s a function in Backendless to get the current timestamp (in UNIX code) and perform mathematical functions (for eg. add 86400000 milliseconds) in the request URL.

But it’s ok, I understand that that shouldn’t be the case. Any calculations should be done on the client side, not in the API call.

Thanks for the help, though!