duanfangfei5558
duanfangfei5558
2016-09-14 14:02

使用GORM和Postgresql时如何在Go中节省数据库时间？

已采纳

I'm currently parsing a time string and saving it to the db (Postgresql):

event.Time, _ := time.Parse("3:04 PM", "9:00 PM")
// value of event.Time now is: 0000-01-01 21:00:00 +0000 UTC
db.Create(&event)

It's giving me this error: pq: R:"DateTimeParseError" S:"ERROR" C:"22008" M:"date/time field value out of range: \"0000-01-01T21:00:00Z\"" F:"datetime.c" L:"3540"

event.Time⁠⁠⁠⁠'s type is time.Time.

I also tried setting event.Time's type to string and using time data type in postgresql:

type Event struct {
  Time string `gorm:"type:time
}

But now I'm getting an error when fetching records in the db:

sql: Scan error on column index 4: unsupported driver -> Scan pair: time.Time -> *string
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dou5454954610 dou5454954610 5年前

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

    See this part of code from dialect_postgres.go:

    case reflect.Struct:
   if _, ok := dataValue.Interface().(time.Time); ok {
      sqlType = "timestamp with time zone"
}

    So basically I see two options for you:

    Either use varchar(10) in DB, and string in Go, an simply save it as "9:00 PM" (where 10 is some number that suits you)

    Or use timestamp with time zone in DB, time.Time in Go, and format your date part as a constant date, 01/01/1970, for example:

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

    In that case you'll have to omit the date part in your presentation, but if you plan to select by date range, that could work better for you.

    点赞 评论 复制链接分享
  • dongyoulou4829 dongyoulou4829 2年前

    From Postgres perspective the error stems from there being no year 0000. If you don't the date you may just be able to add 1 year to the converted timestamp giving '0001-01-01T21:00:00+00' which is a valid Postgres timestamp.

    select '0000-01-01T21:00:00+00'::timestamptz at time zone 'UTC' 
ERROR:  date/time field value out of range: "0000-01-01T21:00:00+00"

    Gives he same error. And just as a demonstration 1 day before 0001-01-01 gives:

    select '0001-01-01T21:00:00+00'::timestamptz at time zone 'UTC' - interval '1 day' "day_before_1/1/1";
--day_before_1/1/1
--0001-12-31 21:00:00 BC
    点赞 评论 复制链接分享
  • duanhuayong6687 duanhuayong6687 2年前

    When updating the DATETIME field in SQL, the Go string must be in this format: time.Now().Format(time.RFC3339).

    点赞 评论 复制链接分享
提交

为你推荐