SQLite-net-pcl Comparing Dates-CodePudding

As stated in the question I am attempting to do a date comparison for tasks that are done within a specific date range.

My model has a boolean called "Done" and a string that stores dates of completion called "DatesCompleted"

I currently have a query on that shows me all Done items limited to 25 and it works just fine:

return db.QueryAsync<Tasks>("SELECT * FROM [Tasks] WHERE [Done] = 1 LIMIT 25");

How can I get only dates completed that are 7 days prior to today? This is kind of sudo code how I'd expect to get the solution but I don't know how to write it out in SQLite-net-pcl,

return db.QueryAsync<Tasks>("SELECT * FROM [Tasks] WHERE [Done] = 1 AND [DatesCompleted] >= (DateTime.Now - new TimeSpan(7,0,0,0))` LIMIT 25");

Edit: After attempting the first solution I am having no luck, all items regardless of completion time appear to show up. I tried this:

 foreach(var ii in TaskListDoneSource)
                {
                    System.Diagnostics.Debug.WriteLine(ii.Name   " completed on : "   ii.DateCompleted   "  compared against "   (DateTime.Now-new TimeSpan(7,0,0,0)).ToString());
                    if (ii.DateCompleted > (DateTime.Now - new TimeSpan(7, 0, 0, 0)))
                        System.Diagnostics.Debug.WriteLine("The date completed has not quite hit 7 days");

                }

The IF statement shows me that it is working when I am within 7 days but outside obviously no IF statement call. So that tells me I'm doing something wrong with the query recommended in first answer:

SELECT * FROM [Tasks] WHERE [Done] = 1 AND [DateCompleted] >= datetime('now', '-7 day')

EDIT 2: I got it working with that query provided. Only thing that was off was off was having the DateCompleted as a DateTime instead of a string that was formatted properly in my model. (https://www.sqlite.org/lang_datefunc.html)

Thanks for the assists.

CodePudding user response：

Assuming that in [DatesCompleted] you store timestamps in ISO format YYYY-MM-DD hh:mm:ss, the correct SQLite syntax to get a timestamp exactly 7 days before the current timestamp is:

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

or:

datetime('now', '-7 day', 'localtime')

to get the timestamp in local time.

so your query should be:

SELECT * 
FROM [Tasks] 
WHERE [Done] = 1 AND [DatesCompleted] >= datetime('now', '-7 day')