Dates without time

[grosch]

When the database type is a date (where there's no time component) and I try to .filter() against it, it doesn't currently work properly. The generated SQL needs to include the ::DATE type specifier.

So for example, I want this query

SELECT *
FROM foo
WHERE foo.day = '2020-01-01'

So you'd immediately do something like so

let day: Date = ...
Foo.query(on: req.db).filter(\.$day == day)...

What that will actually generate is something more like this:

SELECT * 
FROM foo
WHERE foo.month = '2020-01-01 12:37:18 +0000'

and so it'll never match. What it needs to generate is this:

SELECT * 
FROM foo
WHERE foo.month = '2020-01-01 12:37:18 +0000'::DATE

Of course the ideal state would be to just not include the time components at all :)

[tanner0101]

::DATE is a Postgres-specific syntax, though casting may exist in the other dialects. I'm not sure. More research would need to be done there to take a casting approach.

Another way to achieve this would be to have a DateWithoutTime type in Fluent. This has been brought up a few times before. This would not require any core changes to Fluent and would probably solve a lot of other problems, too.

I think the real problem here is that Swift.Date should really be called Swift.Datetime or Swift.Timestamp. I'm not sure silently discarding time information (as casting Swift.Date to ::DATE would do) is the right approach.

This is related to vapor/fluent-postgres-driver#100

[grosch]

Sure, this one is postgres specific, but we have a postgres specific driver, so I think it would be a safe thing to handle it there.

In the MySQL and sqlite drivers, you'd pass it to the date() method.

[0xTim]

We probably need to create a new Swift type DateOnly (or similar) to encapsulate just a date and make it hook in with Fluent. That should solve the issue