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

huangapple go评论131阅读模式
英文:

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

问题

我目前正在解析一个时间字符串并将其保存到数据库(PostgreSQL)中:

event.Time, _ := time.Parse("3:04 PM", "9:00 PM")
// event.Time 的值现在是:0000-01-01 21:00:00 +0000 UTC
db.Create(&event)

它给我返回了这个错误: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 的类型是 time.Time

我还尝试将 event.Time 的类型设置为字符串,并在 PostgreSQL 中使用时间数据类型:

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

但是当从数据库中获取记录时,我遇到了错误:

sql: Scan error on column index 4: unsupported driver -> Scan pair: time.Time -> *string
英文:

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

答案1

得分: 6

进一步调查了这个问题。目前,GORM不支持除了timestamp with time zone之外的任何日期/时间类型。

dialect_postgres.go中可以看到以下代码片段:

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

所以基本上我看到你有两个选择:

要么在数据库中使用varchar(10),在Go中使用string,并将其保存为"9:00 PM"(其中10是适合你的数字)

要么在数据库中使用timestamp with time zone,在Go中使用time.Time,并将日期部分格式化为一个固定的日期,例如01/01/1970:

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

在这种情况下,你将需要在展示时省略日期部分,但如果你计划按日期范围选择,这可能对你更好。

英文:

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.

答案2

得分: 2

你可以使用Gorm的sql标签来设置任意特定于数据库的类型。

type Event struct {
  Time time.Time `sql:"type:timestamp without time zone"`
}
英文:

You can set an arbitrary database-specific type with Gorm using sql tag

type Event struct {
  Time time.Time `sql:"type:timestamp without time zone"`
}

答案3

得分: 0

当在SQL中更新DATETIME字段时,Go字符串必须采用以下格式:time.Now().Format(time.RFC3339)

英文:

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

答案4

得分: 0

从Postgres的角度来看,错误是因为没有0000年。如果你不指定日期,你可以将转换后的时间戳加1年,得到一个有效的Postgres时间戳'0001-01-01T21:00:00+00'。

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"

会得到相同的错误。作为演示,0001-01-01的前一天是:

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
英文:

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

huangapple
  • 本文由 发表于 2016年9月14日 22:02:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/39492468.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定