Feature request: Support ::Date

[grosch]

When the model contains something of type Date the generated SQL call should include ::DATE on it. This comes into play when you have a Date in swift, and then pass that into a .filter() call. Without the ::Date we have to manually remove the time and deal with the timezone issue in the swift code.

[grosch]

This should also be something we can explicitly put. It's automatic if the database type is date, but if it's a timestamptz I still will frequently want to filter by all rows on a certain day, ignoring the time.

Any ETA, @tanner0101, on when you think this might be possible?

[tanner0101]

@grosch I don't think it would be possible to have Fluent automatically detect which type of column is being compared against in order to know whether ::DATE should be added. Given that Swift's Date struct is equivalent to TIMESTAMP in SQL anyway, I think it could make sense to use a different type. Something like PostgresDate or DateWithoutTime. A separate type would be able to serialize itself as DATE without needing to know what it is being compared against.

The other option would be to add some nicer conveniences for doing custom Postgres casting in Fluent. I like this option less though because, unlike DateWithoutTime, it's less generic.

[grosch]

It needs the option unfortunately to be fully usable. For example right now I have a timestamptz column but I have a need to find all items updated on a specific date. If I had to only use a custom type that wouldn’t work.

[grosch]

Any thoughts on when you may be able to work this one out, @tanner0101 ?

[tanner0101]

@grosch could you include some code samples to help me better understand this? Including the desired SQL queries would help, too.

[grosch]

So an example SQL would be this:

SELECT clientID, SUM(amount) amount
FROM PrepaidAddition
WHERE month = '2019-01-12 14:00:00'::DATE
GROUP BY clientID

And so I'd use something like this right now:

        conn.select()
            .column(\PrepaidAddition.clientID)
            .column(\PrepaidAddition.amount)
            .from(PrepaidAddition.self)
            .where(\PrepaidAddition.month == thisMonth)
            .groupBy(\PrepaidAddition.clientID)

which of course never returns any rows because the month includes the time by default and that doesn't match. So I need some way to tell the where clause that the SQL written out should include that ::DATE part on it.

[tanner0101]

@grosch is PrepaidAddition.month a Date value in Swift and TIMESTAMP value in Postgres?

[grosch]

No, it's a date in PostgreSQL as well because I don't care about tracking the time, just the day.

[grosch]

builder.field(for: \.month, type: .date, .notNull)

[tanner0101]

I see. I think for this a good solution would be implementing a PostgresDate type that is actually a date. Not a timestamp like Swift.Date.

If you conform that type to PostgreSQLDataConvertible you will be able to specify the DATE type there and not require a cast. You could also change your model to using that type for the month property.

(For reference, here's Swift.Date's conformance to that protocol)

It might be a good idea to add this type to the PostgreSQL anyway since Swift.Date has its drawbacks.

That said, I do still think it would be useful for Fluent to support casting. But perhaps that should be a separate feature request.

[grosch]

I'm not sure how that will work for me. Remember part of why this came up is the calendar functions which allow me to strip out the time don't work on server side Swift yet. That's why the generated SQL had to have the ::DATE explicitly added to it.

[tanner0101]

Were you using DateComponents? AFAIK, that should be able to get you the year, month, date, etc on Linux just fine.

But still, you shouldn't need to use DateComponents at all here to communicate with the database at least. PostgresDate would look something like:

struct PostgresDate: PostgreSQLCustomDataConvertible {
    var day: Int
    var month: Int
    var year: Int
    ...
}

With your model looking like:

struct Foo: Model {
    ...
    var date: PostgresDate
    ...
}

And comparison:

Foo.query(on: ...).filter(\.date == .init(day: 1, month: 2, year: 2018))