Golang gorm time data type conversion

Question

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)
);

Your code works in my environment and able to correctly insert data into database. From the error message, it seems that the error is happening when you ore creating `building`. can you paste the code? — ymonad, Feb 04 '17 at 07:42
I already tried setting the type of start_time and end_time for TIME, DATE, and TIMESTAMP, and all works, at least for `time.Now()`. here's the code: https://gist.github.com/anonymous/5014d44ca999144ff06ffc05aa4acee3 — ymonad, Feb 04 '17 at 07:49
So as @VonC mentions in the answer, you may update the gorm library to the newest one, or try VonC's answer. — ymonad, Feb 04 '17 at 07:50
@ymonad, I see, you use time.Now() which gives complete date and time, but I use a string like "10:00:00" — Rustam Ibragimov, Feb 04 '17 at 09:06
@RustamIbragimov were you able to find a fix/workaround for that? I'm just starting to look at how to handle mysql TIME. — Alan, Feb 26 '20 at 12:52

Ezequiel Muns · Answer 1 · 2020-12-26T18:08:05.673

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

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

As mentioned in "How to save time in the database in Go when using GORM and Postgresql?"

Currently, there's no support in GORM for any Date/Time types except timestamp with time zone.

So you might need to parse a time as a date:

time.Parse("2006-01-02 3:04PM", "1970-01-01 9:00PM")

edited May 23 '17 at 10:29

Community

1
1

answered Feb 04 '17 at 07:40

VonC

1,262,500
529
4,410
5,250

score 0 · Answer 3 · answered Dec 23 '20 at 14:42

I am have come across the same error. It seems like there is a mismatch between type of the column in the database and the Gorm Model

Probably the type of the column in the database is text which you might have set earlier and then changed the column type in gorm model.

Golang gorm time data type conversion

3 Answers3