Golang gorm time data type conversion

Rustam Ibragimov picture Rustam Ibragimov · Feb 4, 2017 · Viewed 12k times · Source

Situation:

I'm using a postgres database and have the following struct:

type Building struct {
ID        int `json:"id,omitempty"`
Name      string `gorm:"size:255" json:"name,omitempty"`
Lon       string `gorm:"size:64" json:"lon,omitempty"`
Lat       string `gorm:"size:64" json:"lat,omitempty"`
StartTime time.Time `gorm:"type:time" json:"start_time,omitempty"`
EndTime   time.Time `gorm:"type:time" json:"end_time,omitempty"`
}

Problem:

However, when I try to insert this struct into the database, the following error occurs:

parsing time ""10:00:00"" as ""2006-01-02T15:04:05Z07:00"": cannot parse "0:00"" as "2006""}.

Probably, it doesn't recognize the StartTime and EndTime fields as Time type and uses Timestamp instead. How can I specify that these fields are of the type Time?

Additional information

The following code snippet shows my Building creation:

if err = db.Create(&building).Error; err != nil {
    return database.InsertResult{}, err
}

The SQL code of the Building table is as follows:

DROP TABLE IF EXISTS building CASCADE;
CREATE TABLE building(
  id SERIAL,
  name VARCHAR(255) NOT NULL ,
  lon VARCHAR(31) NOT NULL ,
  lat VARCHAR(31) NOT NULL ,
  start_time TIME NOT NULL ,
  end_time TIME NOT NULL ,
  PRIMARY KEY (id)
);

Answer

Ezequiel Muns picture Ezequiel Muns · Dec 26, 2020

While gorm does not support the TIME type directly, you can always create your own type that implements the sql.Scanner and driver.Valuer interfaces to be able to put in and take out time values from the database.

Here's an example implementation which reuses/aliases time.Time, but doesn't use the day, month, year data:

const MyTimeFormat = "15:04:05"

type MyTime time.Time

func NewMyTime(hour, min, sec int) MyTime {
    t := time.Date(0, time.January, 1, hour, min, sec, 0, time.UTC)
    return MyTime(t)
}

func (t *MyTime) Scan(value interface{}) error {
    switch v := value.(type) {
    case []byte:
        return t.UnmarshalText(string(v))
    case string:
        return t.UnmarshalText(v)
    case time.Time:
        *t = MyTime(v)
    case nil:
        *t = MyTime{}
    default:
        return fmt.Errorf("cannot sql.Scan() MyTime from: %#v", v)
    }
    return nil
}

func (t MyTime) Value() (driver.Value, error) {
    return driver.Value(time.Time(t).Format(MyTimeFormat)), nil
}

func (t *MyTime) UnmarshalText(value string) error {
    dd, err := time.Parse(MyTimeFormat, value)
    if err != nil {
        return err
    }
    *t = MyTime(dd)
    return nil
}

func (MyTime) GormDataType() string {
    return "TIME"
}

You can use it like:

type Building struct {
    ID        int    `json:"id,omitempty"`
    Name      string `gorm:"size:255" json:"name,omitempty"`
    Lon       string `gorm:"size:64" json:"lon,omitempty"`
    Lat       string `gorm:"size:64" json:"lat,omitempty"`
    StartTime MyTime `json:"start_time,omitempty"`
    EndTime   MyTime `json:"end_time,omitempty"`
}

b := Building{
    Name:      "test",
    StartTime: NewMyTime(10, 23, 59),
}

For proper JSON support you'll need to add implementations for json.Marshaler/json.Unmarshaler, which is left as an exercise for the reader 😉