How to compare Date?

[iWECon]

Model:

  final class Record: Model {
      static var schema: String = "records"

      @ID(key: .id)
      var id: UUID?

      @Field(key: "name")
      var name: String

      @Timestamp(key: "date", on: .create, format: .default)
      var date: Date?

      @Field(key: "times")
      var times: Int

      init() { }

      init(name: String) {
          self.name = name
      }
  }

Use:

let date = Date()

try await Record.query(db: req.db)
                .filter(\.$date == date) // can't compare
                .first()

Can't compare date, and I saw the log, the $date bind property is Optional...

[ DEBUG ] SELECT "records"."id" AS "records_id", "records"."name" AS "records_name", "records"."date" AS "records_date", "records"."times" AS "records_times" FROM "records" WHERE "records"."name" = $1 AND "records"."date" = $2 LIMIT 1 ["AppleReleaseNotesCron", Optional(2022-04-10 15:21:00 +0000)] [database-id: server-health] (FluentPostgresDriver/FluentPostgresDatabase.swift:29)

⚠️ ["AppleReleaseNotesCron", Optional(2022-04-10 15:21:00 +0000)]

I found a way to use, but I think it is very inconvenient to use:

.filter(\.$date == .custom("'\(date)'"))

Is there any other way to handle properly?

[iWECon]

@tanner0101

[0xTim]

let date = Date()

try await Record.query(db: req.db)
                .filter(\.$date == date) // can't compare
                .first()

Should work. Have you got import Fluent in the file?

[iWECon]

@0xTim Yes, I got import Fluent in the file.

Details:

Database:

[0xTim]

What does your migration look like?

[iWECon]

of course,

Record Model:

Record Migration:

ReleaseNotes Model:

ReleaseNotes Migration:

I saw the docs: docs.vapor.codes/timestamp-format

[0xTim]

In the migration you should be using .datetime and not .date

[iWECon]

All-right~

But I have a new problem, I changed it from .date to .datetime and ran vapor run migrate, nothing happened.

❯ vapor run migrate

Building for debugging...
[12/12] Linking Run
Build complete! (3.96s)
Migrate Command: Prepare
No new migrations.

.....why?

I tried:

[0xTim]

Migrations will be run more than once. There's no way for Vapor to know how the user wants to convert existing data. So you either need to wipe the DB and start from scratch (recommended), do it manually (not recommended) or write a new migration to change the column type (which you should do if you have existing data you can't lose or recreate easily

[iWECon]

OK, I got it.

thank you ~

[iWECon]

I have an idea:

Is it possible to save the information of prepare to the _fluent_migrations table?

If possible, we will be able to compare new data with old data in the table(_fluent_migrations) next time.

[0xTim]

That's a lot of random information to store and compare. One day it may be possible to automate but for now it's how it is

[0xTim]

Is your problem now fixed?

[iWECon]

It seems that there is no repair, the problem still exists @0xTim

I have deleted data and recreated using migrate.

fluent logger:

[ DEBUG ] SELECT "release_notes"."id" AS "release_notes_id", "release_notes"."tech_id" AS "release_notes_tech_id", "release_notes"."version_id" AS "release_notes_version_id", "release_notes"."name" AS "release_notes_name", "release_notes"."path" AS "release_notes_path", "release_notes"."date" AS "release_notes_date" FROM "release_notes" WHERE "release_notes"."date" = $1 LIMIT 1 [Optional(2022-04-11 17:00:57 +0000)] [database-id: apple, request-id: 4787E8CE-6937-4223-827C-77B10F8BCA18] (FluentPostgresDriver/FluentPostgresDatabase.swift:29)

[0xTim]

You're comparing exact times, including milliseconds. It's highly likely it's just failing because no record exists for that exact time. Try writing a query to search for a date range (date less than and greater than etc) instead

[iWECon]

I only need to compare the year, month and day, the hours, minutes and seconds can be ignored.

[iWECon]

Ok, use the accurate time to work properly, but this is not what I want.😂

[iWECon]

do you mean use date >= 00:00:00 and <= 23:59:59. If this is, I think .date is more suitable for my needs.

[iWECon]

so be it, I'm going to sleep... have a happy day for you~

[0xTim]

There's no native way in Swift to store just a date, which makes things awkward. What you could do is a raw query value, or custom query or use calendar to get the start and end of a day.

The final option is a new custom type DateOnly that works with the DB and Swift but that's a lot of work

[iWECon]

uhhh, I found a related issue: fluent-kit/issues/273 but it was discussed in 2018-2020, I'm not sure if the feature was done at that time. (I'm busy with other things now, I'll keep reading about it when I'm free)

[0xTim]

Yeah there's been no movement due to no native Swift type. I'm going to close this in favour of that issue, so feel free to comment on that issue. (I'm assuming the underlying issue of comparing dates is solved, you either need to use date time and a date range or a raw query value