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? — Trevor
– Trevor, Commented Apr 25, 2022 at 19:32
It is helpful if I were comparing between specifics but doing datetime.now minus 7 days — Mufacka
– Mufacka, Commented Apr 26, 2022 at 0:32

forpas · Accepted Answer · 2022-04-25 19:47:22Z

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, 2022 at 19:47

answered Apr 25, 2022 at 19:31

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mufacka Over a year ago

Odd enough I am still having issues with this solution. My code is exactly as you posted : return db.QueryAsync<Tasks>("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: sqlitetutorial.net/sqlite-date-functions/… and its same

Mufacka Over a year ago

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

Collectives™ on Stack Overflow

SQLite-net-pcl Comparing Dates

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related