Android Room - Returning last 30 days (1 month) - java

android-room strftime
android-room date
android-room return map
android room data types
room listener android
android-room json
android-room blob
android room lazy loading

I am complete beginner in Android development and got stuck with a fairly simple problem (I guess ;) ).

I am trying to retrieve records from SQLite (using Room) for last 30 days.

This is my query which does not work - it does not return any records (from @Dao Class):

 // getting fares for one day (custom-made query)
@Query("SELECT * FROM fare WHERE createdDateDb >=datetime('now', 'now', '-30 day')")
public List<Fare> getFaresOneDay();

However, this simple query which retrieves all of the records without specified dates does work:

 // getting all fare (via custom-made query)
@Query("SELECT * FROM fare ORDER BY createdDateDb DESC")
public List<Fare> getAllFares();

This is my DateTypeConverter class:

public class DateTypeConverter {

    public long convertDateToLong(Date date) {
        return date.getTime();

    public Date convertLongToDate(long time) {
        return new Date(time);


This is how the value is being assigned to createdDateDb (before it is being inserted into DB):

fare.setCreatedDateDb(new Date());

This is my 'Entity' class:

@Entity(tableName = "fare")
public class Fare {
    private String fareValue;
    private String typeOfJob;

    @PrimaryKey(autoGenerate = true) @NonNull
    private Integer jobId;

    // dates used for user display
    private String createdDate;
    private String createdTime;

    // date var used for comparisons - not to be displayed for the user
    private Date createdDateDb;

and also setter and getter for createdDateDb

// #6 createdDateDb getter
public Date getCreatedDateDb() {
    return createdDateDb;

// #6 createdDateDb setter
public void setCreatedDateDb(Date createdDateDb) {this.createdDateDb = createdDateDb; }

I would really appreciate some hints how to approach it, as it feels like I'm getting nowhere. I have also tried the following:

FROM fare 
WHERE createdDateDb BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime')

Unfortunately it did not work either.


datetime('now', 'now', '-30 day')

This one above returns null,use it correctly

@Query("SELECT * FROM fare WHERE createdDateDb >=datetime('now', '-30 day')")

Android Room - Returning last 30 days (1 month) - java, I am trying to retrieve records from SQLite (using Room) for last 30 days. This is my query which does not work - it does not return any records  We can use the java.util.Calendar class to manipulate the date object. First we create a Calendar Object and set it’s time with a Date Object. Next we can set the Day of the Month to the ‘actual maximum’. The Calendar class knows in which month there are how many days. Finally we return a new Date Object representing the Last day of the


@Query("SELECT * FROM fare WHERE createdDateDb >= DATE('now', '-30 day')")

Calendar, Calendar 's getInstance method returns a Calendar object whose calendar fields have The first week of a month or year is defined as the earliest seven day period designate the week before week 1 of a year as week n of the previous year. DAY_OF_MONTH, 30) before the call to getTime() sets the date to September  Last update on February 26 2020 08:08:14 (UTC/GMT +8 hours) Java Conditional Statement: Exercise-7 with Solution Write a Java program to find the number of days in a month.

I found what was causing the query not to work as intended - the format of the Date stored in createdDateDb, namely if let's assume we were adding the fare now it would be saved in the following way: Wed Aug 29 16:48:55 GMT +01:00 2018

When I changed pattern of how Date is stored in createdDateDb to (yyyy-MM-dd) - Query magically works as expected ;).

            Date c = Calendar.getInstance().getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String formattedDate = sdf.format(c);

Thanks a lot guys for your help - I really appreciate it!

How to use DateTime datatype in SQLite Using Room, For example, 1:30 AM on the date is resolved as 1:30 AM standard time. Ambiguous wall Returns the length of a previous month of the Gregorian calendar. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Android Room - Returning last 30 days (1 month

Room + Time - Android Developers, Android · TensorFlow · Flutter · Firebase · Contact REAL as Julian day numbers​, the number of days since noon in return df.parse(value); 1.SQLite Query to select data between two dates 5.SQLite Query to get Last month Data from User where created_date>=datetime('now', 'now', '-30 day')"). Then, if the current day of the month is greater than 15, it adds one month to the invoicing date. In other words, if it is the 16th or later, invoicing occurs on the 15th of the following month, not of this month. Then it uses the until method to determine the number of days between the current date and the next invoicing date.

Sqlite get a list of last 30 days, one row per day, julianday() returns the Julian Day. strftime() returns a value formatted with your given format string. The first four can be thought of as variations  Problem Description. How to display name of a month in (MMM) format ? Solution. This example shows how to display the current month in the (MMM) format with the help of Calender.getInstance() method of Calender class and fmt.format() method of Formatter class.

Java exercises: Get the last day of the current month, Sqlite get a list of last 30 days, one row per day - android. is same query in android room dao throwing compile error at 'SELECT x+1' x cannot be resolved. UCS Where Month = 11 and Year = 2018 and Event = 1 Union Select Universal_ID ,DL. javascript · android · java · csharp · php · python · ios · jquery · html · sql. All classes of the Java 8 Date/Time API are located within the java.time package. The first class we want to look at is java.time.LocalDate. A LocalDate represents a year-month-day date without time.

  • I have tried this too, and no results are returned from database. I have checked the code again - to see if there is some random mistake - with no luck, but when I change the query to the same one as posted in my first post: getAllFares() - that method works. Any more suggestions how I can find out where the problem is and fix it? Thanks guys for your help so far - I really appreciate it!
  • In this case you would not always get the last 30 days, but dateadd is the way to go
  • Thanks Michael for your input, but as I mentioned in the other comment answering Mihai's post - this solution did not work either. When I remove 'WHERE createdDateDb >=DATE('now', '-30 day')") from the query - it returns all of the records as expected. Is it possible it is something to do with how the date is being stored in createdDateDb or some mistake in DateTypeConverter? I'm stuck on this one ;). Thanks so far for your help.
  • As an aside consider throwing away the long outmoded and notoriously troublesome SimpleDateFormat and friends, and adding ThreeTenABP to your Android project in order to use java.time, the modern Java date and time API. It is so much nicer to work with.