SimpleDateFormat("yyyyMMddHHmmss") problem

Android
1

I cannot get objects form baas with the following whereClouse. I alway get 0 data. What is problem in this WHERE?


DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss", Locale.ENGLISH);
String dateString = df.format(lastTimeUpdated);
whereClause.append(" AND ");
whereClause.append("MyColumn"_dateMain);
whereClause.append(">");
whereClause.append(dateString);
whereClause.append("");
2

Hi Timofey,

why do you need this line of code?:

whereClause.append(" AND ");

Also, the quote in the middle of the argument here does not make sense:

whereClause.append("MyColumn"_dateMain);

The proper formatting of the whereClause would be:

dateColumnName > dateValue

You can test it in BackendlessConsole in your table. Make sure to turn on the “SQL Search” toggle.

Regards,
Mark

3

Hello Mark,

Thanks, SQL search is very useful but:

when I use the following where it not work

(fromUser='123' OR toUser='123') AND dateMain>20141127120647

if I remove brackets it works:

fromUser='123' OR toUser='123' AND dateMain>20141127120647

Is it OK? I am worrying, without brackets it can works incorrect.

4

Additionally, it strange, because in one of my tables it works fine with brackets.

5

Mark, did yu see my problem? I still have it, it seems the time on Baas has own Timezone and I can retrive actual data.

6

The original problem you described had been fixed. Are you experiencing the same problem? Can you specify the exact query that fails for you?

7

Should I get new BaaS Sdk? or this fix was on server side?

I have table with some messages between user, one column is date.
Example:
message 1 has data: 15.12.2014 22:33
message 2 has data: 15.12.2014 22:38
I want get only messages with date > 15.12.2014 22:35
My whereClause:

(fromUser='user1' OR toUser='user2') AND dateMain>20141215223433087

I have a little time buffer. But I nothing get with this whereClause.
If I change the data for example on 20141215183433087 I get latest messages (but not all, so it seems it work)
I think there is something wrong with TimeZone.

8

it is strange because the same work in another table.

9

I apologize, I have misinformed you. The fix I was talking about has not been pushed to production yet, it is currently in QA. I will make sure we update this topic as soon as the deployment takes place (it should not take long).

Regards,
Mark

10

OK, Thanks!
looking forward new version

11

Hi, Timofey,

You have two options to be used in where clause:

  1. Simple long value, which is UNIX time in milliseconds
  2. One of the following date formats:
    MM.dd.yyyy HH:mm:ss MM-dd-yyyy HH:mm:ss MM/dd/yyyy HH:mm:ss

    Note that Backendless API does not support format yyyyMMddHHmmss.

12

That not work. I get error:

code=1017 message=Invalid where clause

The following expressions are incorrect for backendless:

    MM.dd.yyyy HH:mm:ss MM-dd-yyyy HH:mm:ss MM/dd/yyyy HH:mm:ss
13

Do you put datetime value into quotes?
For example:

dateMain>'12.01.2014 00:00:00'
14

Yes, I tried it, but it doesn’t work.

15

On BaaS all datetimes are persisted in GMT0, and on your console you see the datetime in your local GMT. So in your queries you should write a datetime in GMT0. For example, if your local GMT is +3 and on your console you see dates 15.12.2014 22:33 and 15.12.2014 22:38, then to retrieve only the second one your query should contain

dateMain > '12.15.2014 19:34:00'
16

Thanks Sergey! This works!