Problem when making a query with datetime

[aquatyk]

I'm trying to make a query of the day, but I think there is some problem with the conversion that sends the datetime to rest API. because it returns incorrect dates.

DateTime day1 = DateTime.Now.AddDays(-1);
var result = await _client.From().Where(x => x.Created_at >= day1).Get();

The GET method that sends
{https://xxx.supabase.co/rest/v1/Item?created_at=gte.09%2f11%2f2023+10%3a26%3a00}

Thanks

[acupofjose]

Could you verify that the date is saved correctly in your supabase dashboard and that your column is set up to save a timestamp with timezone? My expectation is that there is a problem between a local timestamp and a UTC timestamp being saved on the server.

[aquatyk]

Yes, the column is created as timestamp, Look, the request to the server is in dd MM yyyy format. Doing the query manually, it works. It has to be something with Postgre BaseModel, which doesn't do the conversion well.

        DateTime day1 = DateTime.Now.AddDays(-1);
        string day = day1.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);

            //var result = await _client.From<Item>().Where(x => x.Created_at >= day1).Get();
            var result = await _client.From<Item>().Filter("created_at", Operator.GreaterThanOrEqual, day).Get();

[acupofjose]

Interesting! It does seem to be an issue with assuming universal timestamps in the LINQ expression parser.

Tracking the issue here: supabase-community/postgrest-csharp#76

[acupofjose]

Good catch - it was an assumption that we should convert to ToUniversalTime that was an incorrect assumption to make. This is fixed in [email protected] and [email protected]. Thanks!

[aquatyk]

Hi Joseph

It's strange because the Datetime insert puts the date correctly and when you use LINQ it puts the date format wrong. At the moment you can work well by doing the query without using LINQ. Thanks for your quick response.