Query with date('now') comparison

[Jens Weppner]

Hi: Is it possible to construct a query in ORMLITE like
> SELECT * FROM 'days' WHERE date(date)<date('now');

?

The following does not work as "date('now')" is not interpreted correctly:
>
> QueryBuilder<Day, Object> queryBuilderDay = dayDao.queryBuilder();
> queryBuilderDay
> .where()
> .lt(Day.DATE_FIELD_NAME, "date('now')");

-Jens

[Gray Watson]

On Dec 22, 2010, at 2:39 PM, Jens Weppner wrote:

> The following does not work as "date('now')" is not interpreted correctly:
>>
>>

> .lt(Day.DATE_FIELD_NAME, "date('now')");

So this raw query works:

SELECT * FROM 'days' WHERE date < date('now');

But the queryBuilder example above does not?

.lt(Day.DATE_FIELD_NAME, "date('now')");

- What exception are you getting?
- What database type and OS is this on?

The default database type for Java Date's is TIMESTAMP (MySQL is DATETIME). Could date() be returning some different type?
gray

[Zainodis]

Hello everyone,

I have a similar issue as the OP, which is driving me nuts. I am using ormlite 4.38 on android

Here's my class that I persist and on which I want to run a query:

public class MyClass {

  @DatabaseField(generatedId = true, columnName = ID_FIELD)

  private long id;

  // The variable "created" refers to java.util.Date

  @DatabaseField(columnName = "created", dataType = DataType.DATE_LONG)

  private Date created;

}

Here is my query:

  DeleteBuilder<MyClass, Long> builder = deleteBuilder();

  // Expiry Date = current time minus 5 hours

  Date expiryDate = getExpiryDate();

  SelectArg expiryDateArg = new SelectArg(expiryDate);

  builder.where().lt("created", expiryDateArg);

  delete(builder.prepare());

I am running a Unittest which does the following:

1. Persist five instances of MyClass
2. One of those five instances has it's created Date set to the current time, another one to the current time minus 3 hours, the other have dates < current time - 5 hours (such as: current time minus 12 hours, 17 hours etc...)
3. So that makes two persisted instances of MyClass, where the created Date is GREATER THAN the expiry Date (as  written in the query, the expiry date is current time minus 5 hours)
4. Now I call above query
5. But instead of 3 out of 5 entries, 4 out of 5 entries get deleted - which is obviously wrong.
6. Fun fact: If I run the unittest in debug and step through the above steps, it works ... but if I add Thread.sleep it fails - so I have no idea why it succeeds in debug and since this cant be right, there must be something wrong with my query....

Generally it seems that no matter how many entries I add (I actually tried that), all of them get deleted but one - which makes me think, there must be something wrong with my query?

Please let me know if you have further questions to the described issue. 

Thanks in advance,

Zainodis