Count rows of a particular day this week sqlite

Question

I would like to create a query which returns the number of rows for a particular day this week. For example, if dayNumber = 1, it should return the count of books where BookSaveTime is Monday. It should return the count for Tuesday if dayNumber = 2, and so on.

var week = DateTime.Today.AddDays(-(((int)DateTime.Today.DayOfWeek+6)%7));
return database.ExecuteScalar<int>("SELECT count(*) FROM Book WHERE BookSaveTime > ?;", week);

Currently, this query returns the number of books since the start of the week - but I am confused as to how to modify it such that it returns the count for a given specified day - eg, Monday or Tuesday.

public MyAppSQLiteDatabase()
{
    try
    {
        database = DependencyService.Get<ISQLiteService>().GetConnection(DbFileName);
        database.CreateTable<Book>();

        CurrentState = "Database created";

    }
    catch (SQLiteException ex)
    {
        CurrentState = ex.Message;
    }
}

public int GetDailyCount(int dayNumber){
    var day = DateTime.Today.AddDays(-(((int)DateTime.Today.DayOfWeek+6)%dayNumber));
    return database.ExecuteScalar<int>("SELECT count(*) FROM Book WHERE BookSaveTime = ?;", day );
}

Book class:

public class Book: INotifyPropertyChanged
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    private DateTime bookSaveTime;
    public DateTime BookSaveTime
    {
        get
        {
            return bookSaveTime;
        }
        set
        {
            if (bookSaveTime != value)
            {
                bookSaveTime= value;
                OnPropertyChanged("BookSaveTime");
            }
        }
    }
}
    

Answer 1

To find a specific weekday info your query should like below =>

SELECT * FROM
(SELECT *, cast (strftime('%w', BookSaveTime) as integer) AS WeekDay
FROM BOOK) T1
WHERE T1.WeekDay=1; --Weekday=1 is MONDAY

To Find the count of specific date use the query below

SELECT WeekDay,COUNT(*) AS WeekDayCount FROM
(SELECT *,strftime('%w',BookSaveTime) AS WeekDay
FROM BOOK) T1
GROUP BY T1.WeekDay

Note: You can add WHERE BookSaveTime BETWEEN '2010-01-01' AND '2010-01-31' inside the T1 to select data from particular date range. Demo Code in DB-Fiddle.