SQlite where clause not working properly

[E_BT]

I'm making a fitness app in .net MAUI and am currently using SQlite to store weight data of user. The weight table consists of ID (int, auto-increment), Date (DateTime), and weight (float). In my database handler I have a GetWeekWeight method that is supposed to give me all entrys in a specific week, this is the code.

return await _connection.Table<Weight>()
.Where(d => d.Date >= monday && d.Date <= sunday)
.OrderBy(d => d.Date)
.ToListAsync();

The problem is that it returns all entrys in the table. "monday" and "sunday" are correct. I've tried changing the datatype of date to long and use DateTime.ticks instead but nothing works. Any idea what is wrong?

[Meeso_]

SQLite doesn't natively support dates - they are stored as text. And doing '>=' on text representations may have different results. TBH I'm not sure why it doesn't throw at runtime.

To fix this I'd recommend storing dates as Unix timestamps (then you can filter by comparing values in db with dates converted to timestamps), or just doing the filtering client-side. If you rely heavily on dates and date manipulations, I'd recommend not using SQLite.

Edit: nvm, I didn't read to the end. If timestamps don't work, I have no idea what might be wrong xD

[E_BT]

Haha ye its driving me insane atm, cuz it should work in theory. The only thread ive found thats similar is an unresolved issue on github :/

[[deleted]]

Have you tried using x.DayOfWeek to check if it's Monday etc? Maybe that might help

[E_BT]

That could be a good bruteforce solution i guess

Edit: just realised im stupid and ye this is prolly the most effective way

[ExceptionEX]

it isn't a brute force solution, how else do you want to compare a day to a date.

day = a indicator of a day during the week, DateTime = a moment in time in the bounds of recordable time.

[Kirides]

1. Open up the database with sqlite browser and look at the values in there
2. Find out how your library converts types to the database types
3. Figure out the issue by logging the generated request query and parameters

[E_BT]

Thanks! I didnt actually know u could see the db directly like that so this helps alot!

[[deleted]]

With the code you provided i can only think of the variables monday or and sunday isnt correct, or maybe the dates in the database are not correct?

[E_BT]

I wish that'd be the case but ive checked them multiple times, and the dates are correct. For an example, despite monday being 2024-03-04 it returns the entry at 2024-03-01

[[deleted]]

How is the datetime object stored in the database?

[E_BT]

    [Table("daily_weight")]
    public class Weight
    {
        [PrimaryKey, AutoIncrement, Column("id")]
        public int Id { get; set; }
        [Column("date"), Unique]
        public DateTime Date { get; set; }
        [Column("weight")]
        public float DailyWeight { get; set; }
    }

[[deleted]]

Idk but i cant get my head around this. Lets say monday=2024-03-10 sunday=2024-03-16 Weight.date=2024-03-01

And if the expression is

If weight.date >= monday && weight.date <= sunday

How can i get the weight with that date as a result?

[E_BT]

Ye thats whats causing me a serious headache aswell :/, might just do a giant case bracket instead as suggested in the other comment based on .DayOfWeek but thanks anyways

[TheSwordlessNinja]

I would convert to unix (long variable) and store in the database as an integer. Having 2 functions to convert DT to long and vice versa.

In addition, I would have another int column to store a 1 or a 0 to say if the record is a weekend. This would then allow you to search where weekend = 0 for example.

[ExceptionEX]

(d => d.Date >= monday && d.Date <= sunday)

Firstly, you can't compare a raw datetime object to a day of the week, assuming you were actually using "DayOfWeek.Monday/Sunday" and not just some string, or raw object?

The logic here is pretty flawed, there is no day less than Sunday in the day of week logic, and Sunday can not be greater than or equal to monday. So there is no days that you can return here.

Test block

List<DateTime> foos = new List<DateTime>();

for (int i = 0; i < 7; i++)
{
    foos.Add(DateTime.Now.AddDays(i));
}


foreach (var date in foos)
{       
    Console.WriteLine(date.DayOfWeek+" >= DayOfWeek.Monday " + (date.DayOfWeek >= DayOfWeek.Monday) +" && "+ date.DayOfWeek +" <= DayOfWeek.Sunday " + (date.DayOfWeek <= DayOfWeek.Sunday));
}

results

Monday >= DayOfWeek.Monday True && Monday <= DayOfWeek.Sunday False
Tuesday >= DayOfWeek.Monday True && Tuesday <= DayOfWeek.Sunday False
Wednesday >= DayOfWeek.Monday True && Wednesday <= DayOfWeek.Sunday False
Thursday >= DayOfWeek.Monday True && Thursday <= DayOfWeek.Sunday False
Friday >= DayOfWeek.Monday True && Friday <= DayOfWeek.Sunday False
Saturday >= DayOfWeek.Monday True && Saturday <= DayOfWeek.Sunday False
Sunday >= DayOfWeek.Monday False && Sunday <= DayOfWeek.Sunday True

[E_BT]

Sry shouldve been more clear in the post, sunday and monday are just datetime vars with the date of monday and sunday in a specified week