GoLang中的带时区的时间戳

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

Timestamp with Timezone in GoLang

问题

我发起了一个HTTP调用,并从较大的JSON对象中解析了一个createdTimestamp字段:

CreatedTimestamp string `json:"createdTimestamp"`

从HTTP调用中接收到的_createdTimestamp_的示例将是:"2021-07-19T18:51:23"。

它不会自动转换为time.Time,所以它实际上只能接受一个字符串类型,之前它是有效的,直到PostgreSQL中的类型更改为timestamp with timezone,它的格式如下所示:(yyyy-MM-dd HH:mm:ss.ffffff)。之前它是without timezone

我能够为time.Time对象实现自定义的解组方法,甚至可以格式化它,以便输出类似于2021-07-19 18:51:23.+00,但是这种格式PostgreSQL不接受。

当我尝试插入这个值时(通过GoLang,PostgreSQL驱动程序),我看到的确切错误是:pq: invalid input syntax for type timestamp with time zone: ""

这个错误发生在我尝试使用db.Exec执行插入操作之后,其中db的类型是*sql.DB

我正在使用Go进行这个操作,非常感谢任何帮助!

解组JSON的代码

type CustomTime struct {
	time.Time
}

func (ct *CustomTime) UnmarshalJSON(b []byte) (err error) {
	timeToInsertIntoDb := strings.Trim(string(b), "\"")
	if timeToInsertIntoDb == "null" {
		ct.Time = time.Time{}
		return
	}

	timeToInsertIntoDb = timeToInsertIntoDb + "Z"
	ct.Time, err = time.Parse(time.RFC3339, timeToInsertIntoDb)
	return
}

关于格式化,它取决于输出,但我确实收到了格式的输出。所以如果我这样做,CustomTime.Time.Format(2006-02-01 15:04:05.-07),我将得到2021-07-19 18:51:23.+00的输出。

不过,此时,我甚至不确定Timestamp with Timezone所需的确切格式,关于Golang Postgres驱动程序的文档并不多。

如果需要更多信息,请提问。我正在尽力组织这个问题。

编辑1


如建议所示,我尝试在从HTTP调用中获取的时间戳后附加一个'Z'。在使用time.RFC3339进行解析后,我得到的时间是2021-07-19T18:51:23Z,但仍然失败(给出了上面提到的相同语法错误)。我以几种不同的方式尝试了这种解析。使用我上面提到的格式进行格式化,以及使用它的.String()方法进行格式化,它将给出2021-07-20 18:51:23 +0000 UTC。两种方式都以pq: invalid input syntax for type timestamp with time zone: ""失败。

在解组期间的代码更改:

func (ct *CustomTime) UnmarshalJSON(b []byte) (err error) {
	timeToInsertIntoDb := strings.Trim(string(b), "\"")
	if timeToInsertIntoDb == "null" {
		ct.Time = time.Time{}
		return
	}

	timeToInsertIntoDb = timeToInsertIntoDb + "Z"
	ct.Time, err = time.Parse(time.RFC3339, timeToInsertIntoDb)
	return
}

编辑2

还要提到的另一件事是,我正在使用"database/sql"包和"github.com/lib/pq"作为Postgres数据库连接的驱动程序。这就是为什么错误来自pq的原因。我只是想澄清一下,因为我知道其他人正在使用gorm。我可以写入其他表,只有这个表有带有时区的时间戳Postgres列。

我使用db.Exec("INSERT INTO db (created_timestamp) VALUES ($1)", obj.createdTimestamp.Time进行调用。我尝试将其作为字符串传递(在之前工作时我是这样做的),但现在我尝试使用_time.Time_变量,因为有人说这样做更好。

英文:

I make a HTTP call and unmarshal a createdTimestamp field from the larger json object:

CreatedTimestamp string `json:"createdTimestamp"`

An Example of what I receive from the HTTP call for the createdTimestamp would be: "2021-07-19T18:51:23".

It won't automatically convert it to a time.Time so the only type it would really accept is a string which was working until the type changed in Postgresql to timestamp with timezone < looks like this according to Postgresql db console >(yyyy-MM-dd HH:mm:ss.ffffff). where as before it was without timezone.
I am able to implement a custom unmarshal method for the time.Time object and even format it so I can output something like 2021-07-19 18:51:23.+00 but this won't be accepted by postgres.

The exact error I see when trying to insert this (via GoLang, postgres driver) is: pq: invalid input syntax for type timestamp with time zone: &quot;&quot;

This error comes after I try to execute an insert using db.Exec -> db being of type *sql.DB.

I'm doing this in Go , any help would be greatly appreciated!

Code to Unmarshal Json

type CustomTime struct {
	time.Time
}

func (ct *CustomTime) UnmarshalJSON(b []byte) (err error) {
	timeToInsertIntoDb := strings.Trim(string(b), &quot;\&quot;&quot;)
	if timeToInsertIntoDb == &quot;null&quot; {
		ct.Time = time.Time{}
		return
	}

	timeToInsertIntoDb = timeToInsertIntoDb + &quot;Z&quot;
	ct.Time, err = time.Parse(time.RFC3339, timeToInsertIntoDb)
	return
}

With formatting, it depends on the output but I do receive the output of whatever the format is. So if I do, CustomTime.Time.Format(2006-02-01 15:04:05.-07)
I will get the output of 2021-07-19 18:51:23.+00

Though at this point, i'm not even sure about the exact format needed for Timestamp with Timezone, there isn't too much documentation on this for the Golang Postgres driver.

If there is any more information needed, please ask. I'm trying my best to organize this question.

Edit 1


As suggested, I tried to append on a 'Z' to the timestamp given from the http call. After doing a parse with time.RFC3339, I am given a time of 2021-07-19T18:51:23Z - this still failed (gave the same syntax error stated above). Tried it a few different ways with this parsing. With it formatted the way I stated above, and with it formatted with it's .String() method which would give 2021-07-20 18:51:23 +0000 UTC. Both failed with pq: invalid input syntax for type timestamp with time zone: &quot;&quot;

Code Changes during Unmarshal:

func (ct *CustomTime) UnmarshalJSON(b []byte) (err error) {
	timeToInsertIntoDb := strings.Trim(string(b), &quot;\&quot;&quot;)
	if timeToInsertIntoDb == &quot;null&quot; {
		ct.Time = time.Time{}
		return
	}

	timeToInsertIntoDb = timeToInsertIntoDb + &quot;Z&quot;
	ct.Time, err = time.Parse(time.RFC3339, timeToInsertIntoDb)
	return
}

Edit 2

Another thing to mention would be that I am using the &quot;database/sql&quot; package with the &quot;github.com/lib/pq&quot; as the Postgres driver for DB connection.
Which is why the error is from pq. Just wanted to clarify cause I know others are using gorm. I can write to other tables, it's just this table having the timestamp with timezone Postgres Column I guess.

I am making the call with db.Exec(&quot;INSERT INTO db (created_timestamp) VALUES ($1)&quot;, obj.createdTimestamp.Time
I've tried passing it along as a string (it's what I did before when it was working) but now this is where i'm at since people say it's better to pass a time.Time variable.

答案1

得分: 4

如果你检查一下提供的ctlayout时间格式与时间格式标准RFC3339非常接近。

const ctLayout = "2006-01-02T15:04:05"
const RFC3339  = "2006-01-02T15:04:05Z07:00"

这是从这篇博文中得到的。

在RFC 3339中,我们还可以从格式中知道时区。它以“Z”语法显示。“Z”表示UTC+0。“Z”代表Zulu时区,与GMT或UTC相同(https://stackoverflow.com/a/9706777/4075313)。因此,如果我们在DateTime上放置Z,它的时区就是UTC+0。

如果我们改变传入的时间并在末尾添加'Z',时间解析器应该能够满足要求。

这是重构后的代码。你可以在playground上找到可工作的代码。

timeToInsertInDB := "2006-01-02T15:04:05" + "Z"

testTime, err := time.Parse(time.RFC3339, timeToInsertInDB)
if err != nil {
    fmt.Println(err) 
}

注意:更详细的时区示例

2019-10-12T07:20:50.52Z      (UTC+0)
2019-10-12T07:20:50.52+00:00 (UTC+0)
2019-10-12T14:20:50.52+07:00 (UTC+7)
2019-10-12T03:20:50.52-04:00 (UTC-4)

Postgres驱动程序期望time.Time。如果解析成功,驱动程序应该能够将其插入数据库并将响应解组为模型结构。在playground上查看这个示例。

编辑1

根据OP的编辑,我更改了使用的驱动程序。我尝试了使用database/sql包和github.com/lib/pq,但无法重现这个问题。插入和选择都正常工作。playground

正如Brits所提到的,最可能的原因是你的代码中存在逻辑错误(但由于你没有分享代码,无法确认)。

英文:

If you check the timeFormat ctlayout provided matches closely with the timeformat standard RFC3339

const ctLayout = &quot;2006-01-02T15:04:05&quot;
const RFC3339  = &quot;2006-01-02T15:04:05Z07:00&quot;

From this blogpost.

> In RFC 3339, we can also know the time-zone from the format. It
> displayed in the “Z” syntax. “Z” means UTC+0. “Z” stands for Zulu
> timezone which is the same with GMT or UTC
> (https://stackoverflow.com/a/9706777/4075313). So if we put Z on the
> DateTime, it’s mean its timezone is UTC+0.

If we change the incoming time and append &#39;Z&#39; at the end the time parser should be satisfied.

Here is a refactored code. You can find working code on playground.

        timeToInsertInDB := &quot;2006-01-02T15:04:05&quot; + &quot;Z&quot;

        testTime, err := time.Parse(time.RFC3339, timeToInsertInDB)
        if err != nil {
                fmt.Println(err) 
        }

Note :- More detailed example of timezones

2019-10-12T07:20:50.52Z      (UTC+0)
2019-10-12T07:20:50.52+00:00 (UTC+0)
2019-10-12T14:20:50.52+07:00 (UTC+7)
2019-10-12T03:20:50.52-04:00 (UTC-4)

The postgres driver expects time.Time. If the parsing is succesfull, driver should be able to insert into the database as well as unmarshall the response in the Model structs. Have a look at this example on playground.

edit 1

As per the OP's edit, I changed the drivers used I tried this with database/sql package and github.com/lib/pq but could not reproduce the issue. The insert and select worked completely fine. playground

As mentioned by Brits, this is most likely cause is a logic error in your code (but cannot confirm this as you have not shared the code).

答案2

得分: 2

你的问题很难回答,因为目前它没有足够的信息让我复制这个问题。

如评论中所提到的,当将时间戳插入数据库时,你可以传递一个time.Time类型的值。这样做意味着库会以适当的格式将时间戳传递给服务器(如果你将来切换到其他数据库,比如SQL Server,你的代码可能仍然能够正常工作)。

如果你传递的是time.Time类型的值,那么时间的来源就不重要了。这意味着你的问题实际上不需要提到从JSON转换的过程——只需确保这个转换成功,并将time.Time类型的值传递给Postgres。

下面的代码演示了这一点(在我的Postgres 13.1上成功运行)——这段代码使用了Shailesh Suryawanshi建议的技术来从JSON中进行转换:

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
	"time"
)

func main() {
	db, err := sql.Open("postgres", "DSN goes here")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 创建表(通常不会在这里创建,但这样演示是自包含的)
	_, err = db.Exec("create temporary table db(created_timestamp timestamp with time zone)")
	if err != nil {
		panic(err)
	}

	jsonTS := `2021-07-19T18:51:23`
	ts, err := time.Parse(time.RFC3339, jsonTS+"Z")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec("INSERT INTO db (created_timestamp) VALUES ($1)", ts)
	if err != nil {
		panic(err)
	}

	// 测试检索值
	rows, err := db.Query("select * from db")
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var t time.Time
		err = rows.Scan(&t)
		if err != nil {
			panic(err)
		}
		fmt.Println(t)
	}

	fmt.Println("Complete")
}

我可以通过运行db.Exec("INSERT INTO db (created_timestamp) VALUES ($1)", "")来复制你的问题;完整的错误信息如下:

panic: pq: invalid input syntax for type timestamp with time zone: ""

可能你的问题是由于Postgres配置中的某些问题导致的;运行上述修改后的代码来连接你的服务器将帮助你确认是否是这个原因。然而,根据上述情况,我认为最可能的原因是你的代码中存在逻辑错误(但由于你没有分享代码,无法确认)。

注意:lib/pq的自述文件中指出:“我们建议使用pgx,它正在积极维护”。

英文:

Your question is difficult to answer because it does not currently contain sufficient information to allow me to replicate the issue.

As mentioned in the comments you can, and should, pass a time.Time when inserting timestamps into the database. Doing this means that the library takes care of passing the timestamp to the server in an appropriate format (and your code is likely to continue working if you move to, for example, SQL Server).

If you are passing a time.Time then the source of that time becomes irrelevant. This means your question does not really need to mention the conversion from JSON at all - just make sure you double check that this conversion is successful and you are passing the time.Time to Postgres.

The code below demonstrates this (and completes successfully for me with Postgres 13.1) - this code converts from JSON using the technique suggested by Shailesh Suryawanshi:

package main

import (
	&quot;database/sql&quot;
	&quot;fmt&quot;
	_ &quot;github.com/lib/pq&quot;
	&quot;time&quot;
)

func main() {
	db, err := sql.Open(&quot;postgres&quot;, &quot;DSN goes here&quot;)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Create the table (would not normally do this here but this means the demo is self contained)
	_, err = db.Exec(&quot;create temporary table db(created_timestamp timestamp with time zone)&quot;)
	if err != nil {
		panic(err)
	}

	jsonTS := `2021-07-19T18:51:23`
	ts, err := time.Parse(time.RFC3339, jsonTS+&quot;Z&quot;)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(&quot;INSERT INTO db (created_timestamp) VALUES ($1)&quot;, ts)
	if err != nil {
		panic(err)
	}

	// Test retrieving the value
	rows, err := db.Query(&quot;select * from db&quot;)
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var t time.Time
		err = rows.Scan(&amp;t)
		if err != nil {
			panic(err)
		}
		fmt.Println(t)
	}

	fmt.Println(&quot;Complete&quot;)
}

I can replicate your issue by running db.Exec(&quot;INSERT INTO db (created_timestamp) VALUES ($1)&quot;, &quot;&quot;); the full error is:

panic: pq: invalid input syntax for type timestamp with time zone: &quot;&quot;

It may be possible that your issue is due to something in the Postgres configuration; running a modified version of the above code against your server will enable you to confirm if that is the case. However, based on the above, I believe the most likely cause is a logic error in your code (but cannot confirm this as you have not shared the code).

Note: the lib/pq readme states: "We recommend using pgx which is actively maintained."

huangapple
  • 本文由 发表于 2021年7月24日 08:28:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/68506136.html
匿名

发表评论

匿名网友

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

确定