Query date field in REST API

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

Hi Andrés,

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

Regards,
Mark

Thanks Mark.

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

Can you show how you structure your query?

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.

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

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

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</img>

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

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

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.

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

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

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

Thanks,
Mark

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

Hi Daniel,

Did you set SQL Search to ON?
http://support.backendless.com/public/attachments/24c1e51c6c8605d5d69058a2d80500f4.png</img>

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

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 :frowning:
Will post some new screenshots, but I do have SQL search on

Attached screenshots.

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

Courses have one-many with course dates

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

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

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