Where date is today in query?

General
1

How do I create a query that gives items where the date is within the 24 hours of today?

I first tried an “=” check but I believe that didn’t match due to timezone issues.

Then I tried a “>” which is more along the lines of what I want anyhow… where “>=” midnight this morning and “<” midnight tomorrow.

The “>” even produced an error. Isn’t that ANSI 92? I couldn’t find anything in the documentation about working with date in where clauses.

Thanks!

2

Wow, I just wasted a bunch of time.

I read WAY too much into “ANSI 92.” Spent time looking at how ANSI 92 DATETIME data types are supposed to work.

Despite looking like datetime data coming back in the JSON response, it looks like Backendless stores datetime values in very novel ways in the backend, or at least as accessed in dataQuery.condition where clauses.

I got to this:

queryDayNum = 10000 * selectedDay.getFullYear() +
    100 * (selectedDay.getMonth() + 1) +
    selectedDay.getDate();
console.debug("Listing activities for " +
    queryDayNum);
dataQuery.condition = "activityDate >= " + queryDayNum +
    "000000 AND activityDate < " + (1 + queryDayNum) + "000000";
console.debug("Condition: " + dataQuery.condition);
response = Backendless.Persistence.of(Schedule).find(dataQuery);

This seems to work.

But it seems very convoluted code.

Plus it’s ignoring timezones.

Please share some sort of information about how datetimes work in Backendless.

I’m wasting a lot of time guessing due to a lack of documentation. I never would have guessed I need to do this off of either inspecting the JSON coming back or reading documentation that the dataQuery.condition is:

“Search with query - a query is an SQL-92 expression (the where clause) referencing data object properties.”

3

Next it seems I then need to do some manual offset work with

Date.getTimezoneOffset()

so that I can get a working >= midnight today in my user’s timezone and < midnight tomorrow in my user’s timezone.

Is this the way to do it with Backendless dates?

(I’ve never seen dates handled like this in years of programming.)

4

Hi Gary,

I’d love to help, but I need to ask for a clarification. Your original question was: “How do I create a query that gives items where the date is within the 24 hours of today?”

My question is “when is today?”. Is it in the timezone of the client or the server? The client could be in any time zone and when it sends a request to the server, it could specify the time relative to where the client app is running.

Regards,
Mark

5

I believe I indicated: “in my user’s timezone”

6

Did the clarification help?

7

This can be changed from Need Answer. I’ve figured it out.

8

how to get backendless server time and date?

9

Create a custom service which returns current date and time.