英文:
How to represent PostgreSQL interval in Go
问题
如何在Go中表示PostgreSQL的间隔(interval)?
我的结构体如下所示:
type Product struct {
Id int
Name string
Type int
Price float64
Execution_time time.Duration
}
我数据库中的execution_time
字段是interval
类型。
英文:
How to represent PostgreSQL interval in Go?
My struct looks like this:
type Product struct {
Id int
Name string
Type int
Price float64
Execution_time ????
}
The execution_time field on my database is interval
.
答案1
得分: 6
我找到的最佳答案是在你的模式中使用bigint
,并在time.Duration
的包装类型上实现Value
和Scan
。
// Duration允许我们在Postgres中的bigint和Go中的time.Duration之间进行转换
type Duration time.Duration
// Value将Duration转换为准备写入数据库的原始值。
func (d Duration) Value() (driver.Value, error) {
return driver.Value(int64(d)), nil
}
// Scan从数据库驱动类型中读取Duration值。
func (d *Duration) Scan(raw interface{}) error {
switch v := raw.(type) {
case int64:
*d = Duration(v)
case nil:
*d = Duration(0)
default:
return fmt.Errorf("无法从%#v中进行sql.Scan() strfmt.Duration", v)
}
return nil
}
不幸的是,你将牺牲在查询中进行间隔算术的能力,除非有一些聪明的人愿意发布bigint
到interval
的类型转换。
英文:
The best answer I've come across is to use bigint
in your schema, and implement Value
& Scan
on a wrapper type for time.Duration
.
// Duration lets us convert between a bigint in Postgres and time.Duration
// in Go
type Duration time.Duration
// Value converts Duration to a primitive value ready to written to a database.
func (d Duration) Value() (driver.Value, error) {
return driver.Value(int64(d)), nil
}
// Scan reads a Duration value from database driver type.
func (d *Duration) Scan(raw interface{}) error {
switch v := raw.(type) {
case int64:
*d = Duration(v)
case nil:
*d = Duration(0)
default:
return fmt.Errorf("cannot sql.Scan() strfmt.Duration from: %#v", v)
}
return nil
}
Unfortunately, you'll sacrifice the ability to do interval arithmetic inside queries - unless some clever fellow wants to post the type conversion for bigint
=> interval
.
答案2
得分: 4
如果您可以遵守time.Duration
的限制,并且只需要秒级的精度,您可以按照以下步骤进行操作:
- 使用SECOND精度创建表格:
...
someInterval INTERVAL SECOND(0),
...
- 将INTERVAL转换为秒:
SELECT EXTRACT(EPOCH FROM someInterval) FROM someTable;
-
使用time.Duration::Seconds将数据插入到预处理语句中。
英文:
If you're OK with conforming to the time.Duration
limits and you need only seconds accuracy you could:
- Create the table with a SECOND precision
...
someInterval INTERVAL SECOND(0),
...
- Convert INTERVAL into seconds:
SELECT EXTRACT(EPOCH FROM someInterval) FROM someTable;
-
Use time.Duration::Seconds to insert data to prepared statements
答案3
得分: 2
一种解决方法是使用一个包装类型来封装time.Duration
类型,并在其上提供sql.Scanner
和driver.Valuer
的实现。
// PgDuration 包装了time.Duration类型,为读取/写入数据库提供了sql.Scanner和driver.Valuer的实现。
type PgDuration time.Duration
在插入到INTERVAL
列时,Postgres似乎对提供的格式非常灵活。调用String()
方法返回的默认格式是被接受的,因此在driver.Value
的实现中,只需调用它即可:
// Value 将PgDuration转换为字符串。
func (d PgDuration) Value() (driver.Value, error) {
return time.Duration(d).String(), nil
}
从Postgres检索INTERVAL
值时,它以一种不太容易被Go解析的格式返回(例如"2 days 05:00:30.000250"
),因此我们需要在sql.Scanner
的实现中进行一些手动解析。在我的情况下,我只关心小时、分钟和秒,所以我实现如下:
// Scan 将接收到的格式为hh:mm:ss的字符串转换为PgDuration。
func (d *PgDuration) Scan(value interface{}) error {
switch v := value.(type) {
case string:
// 将hh:mm:ss的格式转换为time.ParseDuration()可解析的格式
v = strings.Replace(v, ":", "h", 1)
v = strings.Replace(v, ":", "m", 1)
v += "s"
dur, err := time.ParseDuration(v)
if err != nil {
return err
}
*d = PgDuration(dur)
return nil
default:
return fmt.Errorf("无法从%#v中进行sql.Scan() PgDuration", v)
}
}
如果你需要支持其他的时间单位,你可以采用类似的方法,并适当处理额外的单位。
此外,如果你恰好正在使用GORM库来自动迁移你的表,你还需要提供GORM的migrator.GormDataTypeInterface
的实现:
// GormDataType 告诉GORM在PgDuration列上使用INTERVAL数据类型。
func (PgDuration) GormDataType() string {
return "INTERVAL"
}
英文:
One solution is to wrap the time.Duration
type in a wrapper type, and on it provide implementations of sql.Scanner
and driver.Valuer
.
// PgDuration wraps a time.Duration to provide implementations of
// sql.Scanner and driver.Valuer for reading/writing from/to a DB.
type PgDuration time.Duration
Postgres appears to be quite flexible with the format provided when inserting into an INTERVAL
column. The default format returned by calling String()
on a duration is accepted, so for the implementation of driver.Value
, simply call it:
// Value converts the PgDuration into a string.
func (d PgDuration) Value() (driver.Value, error) {
return time.Duration(d).String(), nil
}
When retrieving an INTERVAL
value from Postgres, it returns it in a format that is not so easily parsed by Go (ex. "2 days 05:00:30.000250"
), so we need to do some manual parsing in our implementation of sql.Scanner
. In my case, I only care about supporting hours, minutes, and seconds, so I implemented it as follows:
// Scan converts the received string in the format hh:mm:ss into a PgDuration.
func (d *PgDuration) Scan(value interface{}) error {
switch v := value.(type) {
case string:
// Convert format of hh:mm:ss into format parseable by time.ParseDuration()
v = strings.Replace(v, ":", "h", 1)
v = strings.Replace(v, ":", "m", 1)
v += "s"
dur, err := time.ParseDuration(v)
if err != nil {
return err
}
*d = PgDuration(dur)
return nil
default:
return fmt.Errorf("cannot sql.Scan() PgDuration from: %#v", v)
}
}
If you need to support other duration units, you can start with a similar approach, and handle the additional units appropriately.
Also, if you happen to be using the GORM library to automatically migrate your table, you will also want to provide an implementation of GORM's migrator.GormDataTypeInterface
:
// GormDataType tells GORM to use the INTERVAL data type for a PgDuration column.
func (PgDuration) GormDataType() string {
return "INTERVAL"
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论