Custom Time Types in Go with gorm and Gin

I guess the love affair with Go had to end sooner or later but I hit a major snag today trying to figure out how to store time-only and date-only data types in a database. This all turned out to be a lot more complex that it really needed to.

The basic issue is that gorm appears to be only really capable of dealing with DATETIME types in MySQL. It looks like it is also feasible to make it play nicely with the DATE object but this still caused issues with JSON parsing. As I was trying to implement a repeating appointment object, I need to be able to store start/end dates and times in separate columns and gorm just wasn’t having this.

Anyway, I had to do quite a bit of digging to figure this out as I actually had two different problems. The first problem was to get the data in and out of the database and the second was how to get it in and out of a JSON structure.

First things First

The first step is to define structures to represent both the time and date objects. These strutures will ultimately give us something to hang the JSON parser/encoder and the database parsing off.

type DateOnly struct {
    time.Time
}

type TimeOnly struct {
    time.Time
}

These can now be used in the data structure used to define the database model as follows

type ScheduleItem struct {
    gorm.Model
    Name        string `gorm:"size:255;not null;index:name_idx"`
    StartDate   DateOnly `gorm:"type:DATE;not null;"`
    EndDate     DateOnly `gorm:"type:DATE;"`
    StartTime   TimeOnly `gorm:"not null;"`
    EndTime     TimeOnly `gorm:"not null;"`
}

I’m pretty sure the type date is unnecessary but I’ve left it in there as a reminder. A similar data structure may also be defined to manage the JSON encoding and decoding or you can use the same data structure to do both. I generally use a different data structure for input and out to avoid cluttering my API with the default gorm columns.

type ScheduleItemJSON struct {
    ID          uint     `json:"id"`
    Name        string   `json:"name"  binding:"required,max=255"`
    StartDate   DateOnly `json:"start_date" binding:"required"`
    EndDate     DateOnly `json:"end_date"`
    StartTime   TimeOnly `json:"start_time" binding:"required" `
    EndTime     TimeOnly `json:"end_time" binding:"required" `
}

Unfortunately, I could not get the binding:”required” directive to work properly when deciding input data. As I ended up spending far more time on this than I had intended, I decided to deal with this by doing a post check on the parsed data and leave this problem for another day.

Database Conversions

The basics of the database schema definition are handled by the GormDataType and the GormDBDataType functions. This allows any columns defined as TimeOnly to be migrated as the MySQL TIME type. I’ve not debugged this for any other SQL flavours so your mileage may vary on this.

func (TimeOnly) GormDataType() string {
    return "time"
}

func (TimeOnly) GormDBDataType(db *gorm.DB, field *schema.Field) string {
    return "time"
}

func (timeOnly TimeOnly) Value() (driver.Value, error) {
    if !timeOnly.IsZero() {
        return timeOnly.GetTime().Format("15:04:05"), nil
    } else {
        return nil, nil
   }
}

func (timeOnly *TimeOnly) GetTime() time.Time {
    return timeOnly.Time
}

The equivalent for the DateOnly data type looks pretty similar aside that we use the “date” data type and the time format is obviously different. Note the use of isZero to check for null values. This seems to work pretty well.

We also need a scan function to convert the database result into the correct type. In MySQL, the TimeOnly version of this receives the database result as a string and has to parse a time from this

func (timeOnly *TimeOnly) Scan(value interface{}) error {
    scanned, ok := value.([]byte)
    if !ok {
       return errors.New(fmt.Sprint("Failed to scan Time value:", value))
    }
    scannedString := string(scanned)
    scannedTime, err := time.Parse("15:04:05", scannedString)
    if err == nil {
        *timeOnly = TimeOnly{scannedTime}
    }
    return err
}

The DateOnly version is a little simpler since it receives the database result as a time object so we don’t need to parse the time from a string.

func (date *DateOnly) Scan(value interface{}) error {
    scanned, ok := value.(time.Time)
    if !ok {
       return errors.New(fmt.Sprint("Failed to scan DateOnly value:", value))
    }
    *date = DateOnly{scanned}
    return nil
}

JSON Conversions

The JSON conversion use two functions — MarshalJSON and UnmarshalJSON. Since these are called externally, I have no choice but to mix and match using pointer and value references as this the JSON encoder/decoder appear to require this.

func (timeOnly TimeOnly) MarshalJSON() ([]byte, error) {
    return json.Marshal(timeOnly.GetTime().Format("15:04:05"))
}

func (timeOnly *TimeOnly) UnmarshalJSON(bs []byte) error {
    var s string
    err := json.Unmarshal(bs, &s)
    if err != nil {
       return err
    }
    t, err := time.ParseInLocation("15:04:05", s, time.UTC)
    if err != nil {
       return err
    }
    *timeOnly = TimeOnly{t}
    return nil
}

Conclusion

Once these two operations are in place, you should be nicely set up to properly handle Time Only and Date Only objects in your APIs. This seems like quite a major omission from gorm and the JSON marshaller / unmarshaller and took rather a lot of messing about to get working cleanly.