How to unmarshall time string into time.Time in golang?

Question

I am reading data from multiple tables using JOIN, CONCAT, GROUP_CONCAT, JSON_OBJECT. The data is read into the below mentioned model using gorm.

type OrgUserDisPublisherData struct {
    Disciplines datatypes.JSON `json:"disciplines" example:"[]"`
    User        datatypes.JSON `json:"user"`
}

This process is successfully completed. But then when I try to unmarshal the OrgUserDisPublisherData.Disciplines into another struct which has time.Time datatypes. I am getting the following error parsing time "\"2022-11-03 07:08:09.000000\"" as "\"2006-01-02T15:04:05Z07:00\"": cannot parse " 07:08:09.000000\"" as "T"

Final model used for unmarshalling

type Discipline struct {
        Name              string    `json:"name"`
        Code              string    `json:"code"`
        IsPrimary         uint      `json:"isPrimary"`
        IsAligned         uint      `json:"isAligned"`
        IsTrainingFaculty uint      `json:"isTrainingFaculty"`
        AlignmentDate     time.Time `json:"alignmentDate"`
        UnalignmentDate   time.Time `json:"UnalignmentDate"`
        ExpiryDate        time.Time `json:"expiryDate"`
        ExternalId        string    `json:"externalId"`
        Status            string    `json:"status"`
        CreatedAt         time.Time `json:"createdAt"`
        UpdatedAt         time.Time `json:"updatedAt"`
    }

At the same time, while inserting data into the tables the same model was used and it does not throw any error related to time. How can I handle time while unmarshalling, irrespective of the data that is present against the time property?

You should declare your own time type with custom marshaller if your input is not according to RFC-3339. This post may help you: https://romangaranin.net/posts/2021-02-19-json-time-and-golang/ — Eelco, Nov 13 '22 at 16:43
An alternative to declaring a new type would be to ensure that the timestamp values in the JSON object created by the query have the format expected by `time.Time`'s `UnmarshalJSON`; this you can do with `to_char`, e.g. `to_char(created_at, 'YYYY-MM-DDTHH24:MI:SSTZH:TZM')`. — mkopriva, Nov 13 '22 at 16:48

score 1 · Accepted Answer · answered Nov 13 '22 at 23:40

The problem here is that the default JSON marshalling behaviour for date/time types in GoLang structs is to use ISO8601 formatted date/time strings.

This is identified by the format string in the error message with the T separator between date and time and time-zone suffix. The values in your Discipline JSON string don't conform to this format, lacking both the T separator and any time-zone. Hence the error.

If you can influence the formatting of the JSON string produced by gorm, (not something I'm familiar with so cannot say whether you can or how to do so), then the simplest solution would be to ensure that your JSON string time fields are formatted as ISO8601/RFC3339 strings.

If you have no control over that, then you have two options:

Implement some pre-processing of the JSON via an intermediate map[string]any and reformat the appropriate fields. If the gorm formatting is at least consistent then this could be as easy as splitting the string on the space, remove the final 3dps from the time, append an appropriate timezone (or just Z if the times are UTC) then re-assemble with a T separator.
Use a custom time type with a json.Marshaller implementation that works correctly with the gorm formatted values (you still need to know what time zone applies to the persisted values and apply that correctly when marshalling).

Both of these are vulnerable to a change in the gorm formatting of date/time variables and mis-use (failing to pre-process in the case of Option #1 and mistakenly using time.Time rather than the custom type in the case of Option #2).

For this reason, modifying the formatted output from gorm would be the preferred approach for me, if it is possible.

score 0 · Answer 2 · answered Nov 14 '22 at 08:57

The quickest solution was to format the data/value while reading it from DB, as suggested by @Deltics.

While querying the data from the DB, using the DATE_FORMAT() I am formatting the data into the format required by go/json

DATE_FORMAT(actual_data, '%Y-%m-%dT%TZ')

How to unmarshall time string into time.Time in golang?

2 Answers2