Search code examples
javaandroidandroid-sqliteandroid-roommpandroidchart

strftime('%m',date) returns wrong month value only for 2 months

I've been looking up a few anwers around but I'm still confused about this problem.

I'm using Room and I have a database in which I store dates with the following format: 'YYYY-MM-DDTHH:MM:SS.sss', since I create them using LocalDateTime.now().

I'm trying to perform a query to get the items which date are between start_date and end_date, both with format 'YYYY-MM-DD'. I'm getting the values from a DatePickerDialog, so no time attached.

I tried these 2 queries, both giving me wrong results:

@Query("SELECT strftime('%m-%Y',date) as month, COUNT(*) as total from items 
WHERE date>= :start_date AND date< :end_date GROUP BY strftime('%m-%Y',date)")

LiveData<List<Result>> getResult(String start_date, String end_date);

@Query("SELECT strftime('%m',date) as month, COUNT(*) as total from items 
WHERE date>= :start_date AND date< :end_date GROUP BY strftime('%m',date)")

LiveData<List<Result>> getResult(String start_date, String end_date);

Now I get the month number correctly with both queries ("02" or "02-2020", for example), but not the total count. The issue is it still doesn't count the items which item_date=end_date. Changing the query part from AND date< :end_date to AND date<= :end_date doesn't work, and I want to make sure it counts all records of start and end month, regardless of the time stamp.

I want to count the number of items of each month within the dates selected. For simplicity, I only let the user to select dates within the last year (for example, from July to June). The 2 values I want to get are the number of the month (1-12) and the total of items of that month.

How can I get any of this query to work?

Thanks in advance.

EDIT1: I solved part of the issue, my mistake was that I was using a POJO with wrong data type (I was using an int to get the strftime("%m-%Y", date) part). It was solved by changing it to this:

public class Tuple{

    @ColumnInfo(name="month")
    private String Date; // my mistake was here, had int instead of String
....

}

EDIT2: The post was long, so I'm editing to only leave the relevant information plus what worked for me, in case it helps others: I used the suggested tip of the accepted answer (wrapping dates with date()) and changing the end date limit part to AND date<= :end_date again.

Solution

  • sqlite doesn't have DateTime data type. So your storing date as strings. Your also filtering in your query using Date strings (by comparing to DateTime string). For the queries date arguments, you are using YYYY-MM-DD format while for storage your using YYYY-MM-DD hh:mm:ss. This is a string comparison and such an inconsistent comparison may yield unexpected results.

    I would recommend:

    1. You use date() in sqlite to follow a consistent date format to compare the date strings.

    Or better

    1. Use TypeConverter in Roomand store dates in a format like long and use that for comparison.