Postgres-nio date handling with Swift does not appear to work as one would expect

[duncangroenewald]

Describe the issue

When fetching a date value from a database table and then using that same value to find the record containing that value the query fails to fid a matching record

Vapor version

N/A

Operating system and version

macOS 14

Swift version

Swift 5.10.0-dev

Steps to reproduce

Create a Postgres table with records having a date field e.g. "startDate"

Query the database using PostgresClient and save the value into a Swift Date variable.

Now run a query to fetch the records with "statDate" field equals the date just retrieved.

This appears to fail - perhaps because the Swift internal format is different to the Postgres format.

Outcome

If I cast the input value using to_date(value, 'yyyy-mm-dd') e.g.

SELECT * FROM DATETABLE
WHERE startDate=to_date(value, 'yyyy-mm-dd')

then it seems to work.

I would have expected that Postgres-nio would correctly handle the necessary conversion when constructing the SQL statements.

Additional notes

No response

[duncangroenewald]

I think this fixes the issue - I assume for some reason when the date if fetched from Postgres the Swift date has some slight rounding difference so a subsequent comparison fails.

Strange given we are talking Date not Timestamp.

    var dayOnly: Date? {
        get {
            let calendar = Calendar.current
            let year = calendar.component(.year, from: self as Date)
            let month = calendar.component(.month, from: self as Date)
            let day = calendar.component(.day, from: self as Date)
            
            let components = DateComponents(calendar: calendar, year: year, month: month, day: day, hour: 0, minute: 0, second: 0)
            return calendar.date(from: components)
        }
    }

[duncangroenewald]

Hmm - I was mistaken - it seems I am getting different behaviour depending on where the server is hosted. Testing on my local laptop seems to behave differently to a Postgres server running in AWS.

Any pointers as to have to ensure that date comparisons are always done using the same timezone - I am assuming that is possible the source of the problem.

Still seems odd that fetching a Date from the server and then comparing that same value with the same record set does not match the record.

[duncangroenewald]

It seems the only way to reliably compare dates when using a Swift Date is to do the following:

WHERE somedate = to_date(swiftdatestring, 'yyyy-mm-dd')

Would love to know if there is a cleaner way to use native swift dates with Postgres-nio.

[MahdiBM]

Not a "you" issue, there should definitely be better docs etc... about these, but in the meantime you can take a look at the tests:

I personally was pleasantly surprised that PostgresNIO "just works" when decoding our TIMESTAMP WITH TIMEZONE (aka TIMESTAMPTZ)s from our db. I actually expected there to be some decoding problems, but there were none.

[duncangroenewald]

Not sure this is the same issue as fetching a Date value from a table and storing into a Swift Date variable and then doing a query and passing in the Swift variable as part of the WHERE clause.

So it would require a slightly different test and also would need to be tested in difference scenarios - i.e with server in a different timezone to the client application.

In my case using Swift Date variables resulted in different behaviour when using a local database (i.e. same computer as the client application) vs using an AWS based Postgres server.

What is the easiest way to build and run the tests - I will see if I can replicate the issue via a test - this doesn't seem to be set up as an Xcode project. ?

[duncangroenewald]

Here is the test I used

  struct LPTest {
       let id: String
       let name: String
       let startDate: Date
       let endDate: Date
   }
   func checkLaunchPeriod(launchPeriod: LaunchPeriod) async throws {
       
       let client = PostgresClient(configuration: config!)
       
       // 3. Run the client
       try await withThrowingTaskGroup(of: Void.self) { taskGroup in
           taskGroup.addTask {
               await client.run() // !important
           }
           
           var buf = [LPTest]()
           print("Fetching all launchperiods")
           let rows = try await client.query("""
                                   SELECT id, tstamp, number, name, startdate, enddate, state
                                   FROM launchperiods
                                   order by number desc;
                       """
           )
           
           // Fetch the results
           for try await (id, tstamp, number, name, startdate, enddate, state) in rows.decode((String, Date, Int, String, Date, Date, Int ).self) {
               let object = LPTest(id: id, name: name, startDate: startdate, endDate: enddate)
               buf.append(object)
           }
           
           // Get 'October 2024'
           if let oct = buf.first(where: {$0.name == "October 2024"}) {
               print("1. Found record for october with startdate=\(oct.startDate)")
               
               var buf2 = [LPTest]()
               // Now try and find the record
               let rows2 = try await client.query("""
                                   SELECT id, tstamp, number, name, startdate, enddate, state
                                   FROM launchperiods
                                   WHERE startdate=\(oct.startDate)
                                   order by number desc;
                       """
               )
               
               // Fetch the results
               for try await (id, tstamp, number, name, startdate, enddate, state) in rows2.decode((String, Date, Int, String, Date, Date, Int ).self) {
                   let object = LPTest(id: id, name: name, startDate: startdate, endDate: enddate)
                   buf2.append(object)
               }
               
               if let oct2 = buf2.first {
                   print("2. Fetched record for october with startdate=\(oct2.startDate)")
               } else {
                   print("2. Failed to fetch record for october with startdate=\(oct.startDate) !!")
               }
               
               
               var buf3 = [LPTest]()
               // Now try and find the record
               let rows3 = try await client.query("""
                                   SELECT id, tstamp, number, name, startdate, enddate, state
                                   FROM launchperiods
                                   WHERE startdate=to_date(\(oct.startDate.yyyyMMddFormat), 'yyyy-mm-dd')
                                   order by number desc;
                       """
               )
               
               // Fetch the results
               for try await (id, tstamp, number, name, startdate, enddate, state) in rows3.decode((String, Date, Int, String, Date, Date, Int ).self) {
                   let object = LPTest(id: id, name: name, startDate: startdate, endDate: enddate)
                   buf3.append(object)
               }
               
               if let oct3 = buf3.first {
                   print("3. Fetched record for october with startdate=\(oct3.startDate)")
               } else {
                   print("3. Failed to fetch record for october with startdate=\(oct.startDate) !!")
               }
               
           }
           
           taskGroup.cancelAll()
       }
       
       return
   }

Results below:

1. Found record for october with startdate=2024-10-01 00:00:00 +0000
2. Failed to fetch record for october with startdate=2024-10-01 00:00:00 +0000 !!
3. Fetched record for october with startdate=2024-10-01 00:00:00 +0000

Fetching all launchperiods from AWS server

1. Found record for october with startdate=2024-10-01 00:00:00 +0000
2. Fetched record for october with startdate=2024-10-01 00:00:00 +0000
3. Fetched record for october with startdate=2024-10-01 00:00:00 +0000

The table data was loaded from the same csv file using the /copy command

"681B6C81-9E98-4005-8E58-05B7794E2CAC","2024-01-15 13:37:57.834",69,"August 2024","2024-07-30 00:00:00.000","2024-08-26 00:00:00.000",1
"76D9840F-43CF-4050-998A-832089D05DAC","2024-02-16 15:43:09.928",70,"September 2024","2024-08-27 00:00:00.000","2024-09-30 00:00:00.000",1
"A1F97F59-6D58-452E-A19D-20F9657F71ED","2024-06-18 15:16:11.788",71,"October 2024","2024-10-01 00:00:00.000","2024-10-28 00:00:00.000",1
"F852DDAD-A524-4CFC-9C96-78973932BFF3","2024-06-18 15:16:13.686",72,"November 2024","2024-10-29 00:00:00.000","2024-11-25 00:00:00.000",0
"4D3F6975-01C9-4B7E-BE9A-F4D8F3944395","2024-06-18 15:16:14.401",73,"December 2024","2024-11-26 00:00:00.000","2024-12-28 00:00:00.000",0
"D38F2131-5F68-4EB9-934C-9FCE0C46E3A2","2024-06-18 15:16:15.438",74,"January 2025","2024-12-31 00:00:00.000","2025-01-27 00:00:00.000",0