Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
duncangroenewald opened this issue Oct 6, 2024 · 6 comments
Labels
bug Something isn't working

Comments

@duncangroenewald
Copy link

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 duncangroenewald added the bug Something isn't working label Oct 6, 2024
@duncangroenewald
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Contributor

MahdiBM commented Oct 6, 2024

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:

Screenshot 2024-10-06 at 5 10 37 PM

Screenshot 2024-10-06 at 5 18 50 PM

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
Copy link
Author

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
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants