dshxbrzgi090608692
dshxbrzgi090608692
2018-05-09 17:17

使用gorm在sqlite中的时间列

已采纳

I am trying to query objects from sqlite but getting this error because of the type time:

(sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *time.Time)

my struct is:

type Timeline struct {
ID        string     `json:"id"`
Timestamp *time.Time `json:"timestamp"`

and my database is like this:

CREATE TABLE timelines (id text, timestamp text, ...

and one of the sample rows is:

('Locked in VR', '2018-03-17 10:50:59.548+01:00',...

any ideas? should I have something in the struct like?

Timestamp *time.Time `json:"timestamp" gorm:"time"`
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dop82210 dop82210 3年前

    I am not familiar with gorm, but should not the definition of timestamp of type datetime instead of text? Also: when you tag gorm:"time" the column name should be time and not timestamp, or the tag gorm:"timestamp". But you can leave out the gorm tag.

    To make it simple, you can let gorm create the table:

    db, err := gorm.Open("sqlite3", "test.db")
    db.CreateTable(&Timeline{})
    
    点赞 评论 复制链接分享
  • doulian8742 doulian8742 3年前

    Using this would take care of it:

    type Timeline struct {
        ID        string     `json:"id"`
        Timestamp *time.Time `json:"timestamp" gorm:"type:datetime"`
    }
    

    You could even change the declared type of the Timestamp field to something else, say int64 to represent Unix times. Then you could write a Scanner to read the datetime field into the int64 field.

    type TimeStampUnix int64
    type Timeline struct {
        ID        string        `json:"id"`
        TimeStamp TimeStampUnix `json:"timestamp" gorm:"type:datetime"`
    }
    
    func (t *TimeStampUnix) Scan(src interface{}) error {
        switch src.(type) {
        case time.Time:
            *t = TimeStampUnix(src.(time.Time).Unix())
            return nil
        case []byte:
            // bonus code to read text field of format '2014-12-31 14:21:01-0400'
            //
            str := string(src.([]byte))
            var y, m, d, hr, min, s, tzh, tzm int
            var sign rune
            _, e := fmt.Sscanf(str, "%d-%d-%d %d:%d:%d%c%d:%d",
                &y, &m, &d, &hr, &min, &s, &sign, &tzh, &tzm)
            if e != nil {
                return e
            }
    
            offset := 60 * (tzh*60 + tzm)
            if sign == '-' {
                offset = -1 * offset
            }
    
            loc := time.FixedZone("local-tz", offset)
            t1 := time.Date(y, time.Month(m), d, hr, min, s, 0, loc)
            *t = TimeStampUnix(t1.Unix())
            return nil
        default:
            return fmt.Errorf("Value '%s' of incompatible type '%T' found", string(src.([]byte)), src)
        }
    }
    
    点赞 评论 复制链接分享

相关推荐