Query date field in REST API

[andres (Andrés)]

Hi.

In my Users table i have a date field called fechaNacimiento. One user has value 21-02-1986 (in format dd-MM-yyyy) and i would like to know how to send date in REST API when searching in Users table for this field. I have checked in another question, http://support.backendless.com/responses/querying-datetime-fields, that the right format is YYYYMMDDHHMMSS, but i am not able to make it work.

If I send fechaNacimiento >= 19860221000000, i don´t get any result. If i send fechaNacimiento >= 19860220000000, i get the user in response with “fechaNacimiento”:“02/20/1986 23:00:00 GMT+0000”. I am in Spain, GMT +0100, so I don´t know if i have to include somehow the timezone or i have to send date in GMT +0000. What is the right way to query date fields in REST API?

Regards,
Andrés

[mark-piller]

Hi Andrés,

The format of the date is YYYYMMDDHHMMSS and it must be in the GMT+0000 timezone.

Regards,
Mark

[andres (Andrés)]

Thanks Mark.

[nicolas-zapata]

Hi Mark,

According to the documentation a whole lot of different formats should work now for the date, but in reality only the YYYYMMDDHHMMSS works for me

[mark-piller]

Can you show how you structure your query?

[nicolas-zapata]

For example in the web console i try this

HORA_PEDIDO > 1436120774

the field HORA_PEDIDO is a date field. I get all the objects as a response, although it should only return today’s and yesterday´s items

However if i apply the opposite

HORA_PEDIDO < 1436120774

I get 0 objects.
I tried though the JS API with getTime()

 var dataQueryPedidos = {condition: "HORA_PEDIDO > " + oneWeekAgo.getTime()};

But i get an empty response, which is not true.

[mark-piller]

1436120774 corresponds to “Sat Jan 17 1970 14:55:20”

You should be entering a value in milliseconds. Thus “1436120774000” would be “Sun Jul 05 2015 18:26:14”

I used this page for conversions: http://currentmillis.com/

Mark

[nicolas-zapata]

I also tried with milliseconds in the web console… It still doesn’t work

[mark-piller]

You must be doing something wrong. It works just fine.

Here’s a screen will all the data:
http://support.backendless.com/public/attachments/8f0c5e31ec107ce701db4f4609ade2ac.jpg&lt;/img&gt;

Now a screen with SQL query to get all the objects created before 11/16/2013:
http://support.backendless.com/public/attachments/9bb349fc034819c661261a1a15638d12.jpg&lt;/img&gt;

And now just changing “less than” to the “greater than” sign in the SQL query:
http://support.backendless.com/public/attachments/685f4cfbf0378cbc9c9b84e84c52154a.jpg&lt;/img&gt;

[nicolas-zapata]

Well, you are right. The problem seems to be when modifying the value of the query after it has been made.

If i make a query, then alter the parameter (from created > 1436374835000 to created > 1433374835000) and immediately press enter then it does not return the right objects, but apparently random ones. If i instead delete the query, press enter, and then introduce the query again it works alright.

[mark-piller]

Do you wait for the results the first time you make the query? Or do you immediately start making changes?

[nicolas-zapata]

Yes, i do wait for the results. After they are shown i make the changes and do the new, unsuccessful, query

[mark-piller]

Could you take and attach screenshots showing query and results with the first and then second time you ran it?

Thanks,
Mark

[daniel-tavares]

Hi Mark,
I’ve started using Backendless this week and i’m having the same problems with the dates.

I have 2 tables, a course table and a courseDates table.
Course have a one-to-many relationship with CourseDates.

One of the columns in course dates is “startDate”

So I want to find all Courses that dates.startDate > Today. (1454373769145)

But that returns me all dates for some reason.

If I then go into the CourseDates and perform the same query “startDate > 1454373769145”
It works as expected.

I think it has to do with propagating the query to the relationship table. Courses->CourseDates->startDate

If you could help us it would be great as this is critical to the project i’m doing.

Thank you very much

I’ve attached a screenshot

[mark-piller]

Hi Daniel,

Did you set SQL Search to ON?
http://support.backendless.com/public/attachments/24c1e51c6c8605d5d69058a2d80500f4.png&lt;/img&gt;

It also would be helpful if you could repost your screenshots showing table you search in, the structure it has and the query.

Regards,
Mark

[daniel-tavares]

Hi Mark,
I’m having the same problems are this guy.
http://support.backendless.com/t/yyyymmddhhmmss-format-stopped-working-1-2-days-ago

Using yyyymmddhhmmss works fine, not unix timestamp
Will post some new screenshots, but I do have SQL search on

[daniel-tavares]

Attached screenshots.

Inside CourseDates it works startDate > 1454373769145
But from Courses dates.startDate > 1454373769145 it doesn’t

Courses have one-many with course dates

[mark-piller]

Hi Daniel,

Just to make sure I understand the data in the screenshots: did you expect only two Course objects for this query: dates.startDate > 1454373769145 ?

Regards,
Mark

[daniel-tavares]

Hi Mark,

Yes correct.

Basically the documentation
https://backendless.com/documentation/data/ios/data_search_with_dates.htm

Only works for parent tables, if you try and run the search search query for a relationship using the dot notation it doesnt not work. Only using yyyymmddhhmmss works.

Cheers

[mark-piller]

Hi Daniel,

We were not able to reproduce the problem locally in our test apps. Would it be possible for you to run an export on your app (Manage > Export > All Data Tables) and email me the generated zip file? My email is mark@backendless.com.

Regards,
Mark