c# - Counting the number of rows in an SQLite database for this week

Question

I have the following query that returns the count of books entered in the last 7 days. Is it possible to modify the query so that it only returns the count of those entered since the start of the week?

public int GetWeeklyCount()
 {
       var week = DateTime.Today.AddDays(-7);
       return database.ExecuteScalar<int>("SELECT count(*) FROM Book WHERE bookEntryTime > ?;", week);
 }

question from:https://stackoverflow.com/questions/65946915/counting-the-number-of-rows-in-an-sqlite-database-for-this-week

Answer 1

People vary as to what they mean by the start of a week, but for example if you think a week starts on Monday:

var week = DateTime.Today;
while(week.DayOfWeek != DayOfWeek.Monday)
    week = week.AddDays(-1);
return database.ExecuteScalar<int>("SELECT count(*) FROM Book WHERE bookEntryTime > ?;", week);

You could also straight to the math:

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

But to me it's relatively non-self-documenting for little performance advantage