Search code examples
javaandroidandroid-room

How to bind Query parameter into the datetime using room

I would like to bind a parameter into the datetime method of a sql query. You can see below what I tried. I have tried enum too, but it seems enums are parsed by the name of enum and I can't give -7 days as a value to enum

The query i would like the code to produce looks like this.

SELECT * FROM Part where Timestamp > datetime('now' ,'-7 days')

Code#1

@Query("SELECT * FROM Part where Timestamp >datetime('now' ,'-'+:days+'days')")
List<Part> getPastDays(int days);

Produces query#1

(is incorrect as it does not fetch the data)

SELECT * FROM Part where Timestamp >datetime('now' ,'-'+'7'+'days')

Code#2(Does not compile->error: Unused parameter: days)

@Query("SELECT * FROM Part where Timestamp >datetime('now' ,'- :days days')")
List<Part> getPastDays(int days);

Code#3

@Query("SELECT * FROM Part where Timestamp >datetime('now' ,'-' /:days/ 'days')")
List<Part> getPastDays(int days);

Produces Query#3( is incorrect as does not return data)

SELECT * FROM Part where Timestamp >datetime('now' ,'-' /'7'/ 'days')
Solution

  • The SQLite SQL concatenate is || so :-

    @Query("SELECT * FROM Part where Timestamp >datetime('now' ,'-'||:days||' days')")
List<Part> getPastDays(int days);

    as per The || operator is "concatenate" - it joins together the two strings of its operands.