Unable to decode date

[BalestraPatrick]

Hey there! In my PostgreSQL database I have two types of dates in the same column:

• 2017-12-08 23:20:47.307779+01
• 2017-09-29 02:00:00+02

One looks like to have milliseconds while the other one doesn't. Trying to decode this table from the database results in: FluentError.decodingError: The data couldn’t be read because it isn’t in the correct format. (Accessory nested model)

I tried digging into the FluentQuery codebase and I figured out that the following method returns nil:

override func date(from string: String) -> Date? {
        if let result = super.date(from: string) {
            return result
        }
        return OptionalFractionalSecondsDateFormatter.withoutSeconds.date(from: string)
    }

It seems that it's trying to decode the date with the withoutSeconds formatter and is failing. Do you know what's going on? Are you trying to decode the dates with both milliseconds and without to make sure it can be decoded correctly?
Thanks a lot!

[BalestraPatrick]

I was able to fix the issue for me by changing OptionalFractionalSecondsDateFormatter :

class OptionalFractionalSecondsDateFormatter: DateFormatter {
    static let withoutSeconds: DateFormatter = {
        let formatter = DateFormatter()
        formatter.calendar = Calendar(identifier: .iso8601)
        formatter.locale = Locale(identifier: "en_US_POSIX")
        formatter.timeZone = TimeZone(identifier: "UTC")
        formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ssZZZZZ" //without milliseconds
        return formatter
    }()
    
    func setup() {
        self.calendar = Calendar(identifier: .iso8601)
        self.locale = Locale(identifier: "en_US_POSIX")
        self.timeZone = TimeZone(identifier: "UTC")
        self.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SZZZZZ" //with milliseconds
    }
...

Basically the timezone date formatter symbol was wrong. I am opening a PR to fix it, I am not sure if my database has the wrong format since I am not a PostgreSQL expert, but in case it's a bug in FluentQuery, feel free to merge it 😄

[MihaelIsaev]

Hey @BalestraPatrick thank you for contributing, but unfortunately I can't merge your pull request because it will broke standard postgres dates decoding.

FluentQuery allows you to use custom date formatter while decoding, e.g.

.decode(MyModel.self, dateDecodingStrategy: .formatted(<YOUR_CUSTOM_DATE_FORMATTER>))

So in your case you should implement your own custom date formatter e.g. by copying OptionalFractionalSecondsDateFormatter from FluentQuery's source code

class BPDateFormatter: DateFormatter {
    static let withoutSeconds: DateFormatter = {
        let formatter = DateFormatter()
        formatter.calendar = Calendar(identifier: .iso8601)
        formatter.locale = Locale(identifier: "en_US_POSIX")
        formatter.timeZone = TimeZone(identifier: "UTC")
        formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ssZZZZZ" //without milliseconds
        return formatter
    }()
    
    func setup() {
        self.calendar = Calendar(identifier: .iso8601)
        self.locale = Locale(identifier: "en_US_POSIX")
        self.timeZone = TimeZone(identifier: "UTC")
        self.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SZZZZZ" //with milliseconds
    }
    
    override init() {
        super.init()
        setup()
    }
    
    required init?(coder aDecoder: NSCoder) {
        super.init(coder: aDecoder)
        setup()
    }
    
    override func date(from string: String) -> Date? {
        if let result = super.date(from: string) {
            return result
        }
        return BPDateFormatter(from: string)
    }
}

so now you can use it like this

.decode(MyModel.self, dateDecodingStrategy: .formatted(BPDateFormatter()))

Or even you could create an extension to simplify decoding and force to use BPDateFormatter everywhere

import Foundation
import FluentPostgreSQL
import PostgreSQL
import FluentQuery

class BPDateFormatter: DateFormatter {}

typealias PostgresRow = [PostgreSQL.PostgreSQLColumn: PostgreSQL.PostgreSQLData]

extension EventLoopFuture where T == [PostgresRow] {
    func bpDecode<T>(_ to: T.Type) throws -> EventLoopFuture<[T]> where T: Decodable {
        return map { return try $0.decode(T.self, dateDecodingStrategy: .formatted(BPDateFormatter())) }
    }
}

extension Array where Element == PostgresRow {
    func bpDecode<T>(_ to: T.Type) throws -> [T] where T: Decodable {
        return try map { try $0.decode(T.self, dateDecodingStrategy: .formatted(BPDateFormatter())) }
    }
}

extension Dictionary where Key == PostgreSQL.PostgreSQLColumn, Value == PostgreSQL.PostgreSQLData {
    func bpDecode<T>(_ to: [T.Type]) throws -> T where T: Decodable {
        return try decode(T.self, dateDecodingStrategy: .formatted(BPDateFormatter()))
    }
    
    func bpDecode<T>(_ to: T.Type) throws -> T where T: Decodable {
        let convertedRowValues = map { (QueryField(name: $0.name), $1) }
        let convertedRow = Dictionary<QueryField, PostgreSQL.PostgreSQLData>(uniqueKeysWithValues: convertedRowValues)
        return try FQDataDecoder(PostgreSQLDatabase.self, entity: nil, dateDecodingStrategy: .formatted(BPDateFormatter())).decode(to, from: convertedRow)
    }
}

with that extension you will be able to call .bpDecode(MyModel.self) and FluentQuery will always decode using your BPDateFormatter 🙂

💭Looks like I should add this example to readme! 🎉

Thank you again for spotting that! 👍

[BalestraPatrick]

That makes sense! I remember seeing a way to pass in my DateFormatter but I didn't think about it. I am not sure why my database isn't using the default date encoding, so I guess I'll have to pass it my own formatter. Thanks for clearing that up!

[MihaelIsaev]

It looks like the only difference is that you're using timezones.
So maybe I'm wrong by telling about dates without timezone as about default postgres dates, sorry!
Let me take a look again, I'm just trying to find the right date format strings which will work for both dates with timezones and without.

[MihaelIsaev]

@BalestraPatrick ok, I found the way!
v0.4.22 now support decoding dates with timezone, you could take a look at the commit
@BalestraPatrick Could you please test it in your project and let me know if it works?

[MihaelIsaev]

By the way this is my playground code.
If someone want to try to make it better please feel free to send pull request 🙂

class OptionalFractionalSecondsDateFormatter: DateFormatter {
    func setup() {
        self.calendar = Calendar(identifier: .iso8601)
        self.locale = Locale(identifier: "en_US_POSIX")
        self.timeZone = TimeZone(identifier: "UTC")
        //with milliseconds and without timezone
        self.dateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSS'ZZZZZ"
    }
    
    override init() {
        super.init()
        setup()
    }
    
    required init?(coder aDecoder: NSCoder) {
        super.init(coder: aDecoder)
        setup()
    }
    
    override func date(from string: String) -> Date? {
        //with milliseconds and without timezone
        if let result = super.date(from: string) {
            return result
        }
        //without milliseconds and without timezone
        dateFormat = "yyyy-MM-dd HH:mm:ss'ZZZZZ"
        if let result = super.date(from: string) {
            return result
        }
        //with milliseconds and timezone
        dateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSSZZZZZ"
        if let result = super.date(from: string) {
            return result
        }
        //without milliseconds and with timezone
        dateFormat = "yyyy-MM-dd HH:mm:ssZZZZZ"
        return super.date(from: string)
    }
}

let formatter = OptionalFractionalSecondsDateFormatter()

print("date1: \(formatter.date(from: "2018-07-12 13:46:05.846234"))")
print("date2: \(formatter.date(from: "2018-07-12 13:56:00"))")
print("date3: \(formatter.date(from: "2017-09-29 01:20:47.307779+01"))")
print("date4: \(formatter.date(from: "2017-09-29 01:20:47.307779+0130"))")
print("date5: \(formatter.date(from: "2017-09-29 02:00:00+01"))")
print("date6: \(formatter.date(from: "2017-09-29 02:00:00+0130"))")

[BalestraPatrick]

@MihaelIsaev The new release seems to work perfectly in my project! Thank you very much 👍