Problem decoding PostgreSQL timestamps

[fwgreen]

Trying to list the records in a table with timestamp columns triggers this error:

Error:  702 : typeMismatch(Swift.Double, Swift.DecodingError.Context(codingPath: [CodingKeys(stringValue: "starting", intValue: nil)], debugDescription: "Could not cast Optional(2015-08-02 16:30:00 +0000)", underlyingError: nil))

CREATE TABLE public.availability
(
    id bigint NOT NULL DEFAULT nextval('availability_id_seq'::regclass),
    team_member_fk bigint NOT NULL,
    starting timestamp with time zone,
    ending timestamp with time zone,
    overtime boolean,
    on_shift boolean,
    CONSTRAINT pk_availability PRIMARY KEY (id),
    CONSTRAINT fk_availability_team_member_fk FOREIGN KEY (team_member_fk)
        REFERENCES public.team_member (id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
)
...

struct Availability : Codable, Model {

    var id: Int
    var starting: Date
    var ending: Date
    var onShift: Bool
    var overtime: Bool
    var teamMember: Int
}

extension Availability {
    static var tableName: String = "public.availability"
}

extension Availability {
    enum CodingKeys: String, CodingKey {
      case id, overtime, starting, ending
      case onShift = "on_shift"
      case teamMember = "team_member_fk"
   }
}

[EnriqueL8]

Hey @fwgreen, what version are you using?

[fwgreen]

@EnriqueL8 The Package.resolved says version 0.2.0.

[EnriqueL8]

After replicating your issue - I have seen that we extract the TimeInterval from Date and store it in the database. Since public typealias TimeInterval = Double, we just make the table column of type double precision. In you case since you are constructing the Create Table manually , you have set a different type for that column that is why we fail to decode the value because we are expecting a double instead of a timestamp.

Maybe it makes more sense to save the Date as a timestamp in the Database? A decision to make is to default to timestamp without time zone for the column type when creating the table at runtime and then if that type needs changing then the Create Table statement can be written manually.

I believe the process of decoding from timestamp without time zone and timestamp with time zone should be the same so it should be fairly easy to construct the Date Type. We will implement this change and release a new version as soon as possible.

Thanks for raising this issue @fwgreen!

[fwgreen]

@EnriqueL8 Thanks for taking the time to investigate this issue. My database is part of a preexisting Java application and it seems the PostgreSQL JDBC driver defaults java.time.LocalDateTime to timestamp, a fact I took for granted. These are the risks when running in polyglot environments 😄

[fabrijp]

I'm having the exactly same issue trying to retrieve a datetime field from a preexisting MySQL database.. @EnriqueL8 any date for this implement or fix ?

[kilnerm]

Having done some preliminary work on this the ORM encodes and decodes Date to a double as that is how Date is implemented (see its int(from:) andencode(to:)` functions).

Trying to overload this implementation to allow encoding / decoding from the range of "timestamp" fields the database plugins provide is going to be messy.

I am proposing to add some additional data types to represent the various SQL timestamps and include the necessary encoding / decoding support for them. Where possible some additional convenience API's will be added to the types to allow easy conversion to / from Date

[bhirt]

Having dates stored in the database as a number has been a problem for me. It would be great to be able to use native database format (preferrable with time zone support to). I have a system with lots of calendar computations and pre existing schema and reporting that will not work with a double.

[kilnerm]

I have merged changes to address alternative date encoding formats under PR #110 .

Hopefully they address the majority of use cases, should anything further be required please raise a new enhancement request.