SQLite-net-pcl Comparing Dates

Question

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)

Does this answer your question? [SQLite database - select the data between two dates?](https://stackoverflow.com/questions/29971762/sqlite-database-select-the-data-between-two-dates) — Trevor, Apr 25 '22 at 19:32
It is helpful if I were comparing between specifics but doing datetime.now minus 7 days — Mufacka, Apr 26 '22 at 00:32

forpas · Accepted Answer · 2022-04-25T19:47:22.707

0

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')

edited Apr 25 '22 at 19:47

answered Apr 25 '22 at 19:31

forpas

160,666
10
38
76

Odd enough I am still having issues with this solution. My code is exactly as you posted : return db.QueryAsync("SELECT * FROM [Tasks] WHERE [Done] = 1 AND [DateCompleted] >= datetime('now', '-7 day')"); I have it display the completed times in debug when I load tasks page, completed time is right, I change phone system time by a month and still seeing the item from the query that was over 7 days old. It seems like It should be working, not sure whats wrong. I have double checked with: https://www.sqlitetutorial.net/sqlite-date-functions/sqlite-datetime-function/ and its same – Mufacka Apr 25 '22 at 23:27
I may see what is could be. I just need to confirm. based on that link I just sent, it shows that it is YYYY-MM-DD HH:MM:SS and my date looks like 4/25/2022 4:28:57 PM – Mufacka Apr 25 '22 at 23:34

SQLite-net-pcl Comparing Dates

1 Answers1