Room database, SELECT * FROM table by a given day

Question

I'm using Room Persistence Library in my Android project, and store date using date type converter:

object DateConverter {
    @TypeConverter
    @JvmStatic
    fun fromTimestamp(value: Long?): Date? = if (null == value) null else Date(value)

    @TypeConverter
    @JvmStatic
    fun dateToTimestamp(date: Date?): Long? = date?.time
}

How could I select from my table by a given day using using Room DAO?

Answer 1

 @Query("SELECT * FROM table_name WHERE date BETWEEN :dayst AND :dayet")
 Object getFromTable(long dayst, long dayet);

date dayst will be the timestamp for 00:00, i.e., 09/11/2017 00:00:00

date dayet will be the timestamp for 23:59, i.e., 09/11/2017 23:59:59

The Object is what you are getting from the database.

Answer 2

make it more simple.

  @Query("SELECT * FROM table_name WHERE date BETWEEN :daystart AND :dayend")
List<Student> fetchAllData(long start,long end);

Answer 3

I have taken value of day, month and year by spinner. You can use it(With string and DateTypeConvertor instead Long) like this

At Dao :

@Query("SELECT * FROM MILKMANTABLE WHERE strftime('%Y',date) IN(:year) AND strftime('%m',date) IN(:month) AND strftime('%d',date) IN(:day)")

List<MilkMan> getMonthDetails(String day, String month, String year);

You can use this converter class to convert and save Date type to string in room table

DateTypeConverter :

public class DateConverter {
    static DateFormat df = new SimpleDateFormat(Constants.DOB_FORMAT);

    @TypeConverter
    public static Date fromTimestamp(String value) {
        if (value != null) {
            try {
                return df.parse(value);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return null;
        } else {
            return null;
        }
    }

    @TypeConverter
    public static String dateToTimestamp(Date value) {

        return value == null ? null : df.format(value);
    }
}

At Constants.java:

public static String DOB_FORMAT = "YYYY-MM-dd";