如何在Go中表示PostgreSQL的时间间隔(interval)?

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

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的包装类型上实现ValueScan

// 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
}

不幸的是,你将牺牲在查询中进行间隔算术的能力,除非有一些聪明的人愿意发布bigintinterval的类型转换。

英文:

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

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;

答案3

得分: 2

一种解决方法是使用一个包装类型来封装time.Duration类型,并在其上提供sql.Scannerdriver.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"
}

huangapple
  • 本文由 发表于 2015年9月24日 02:26:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/32746858.html
匿名

发表评论

匿名网友

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

确定