How to store Golang time.time in Postgresql timestamp?

Question

May I know how to store the time.time object in Postgresql?

For example, the SQL query:

INSERT INTO "UserAccount" ("email", "login_time") VALUES ('[email protected]', 2017-12-12 00:58:26.9589451 +0800 +08 m=+1406.914186601)

I tried to use loginTime := time.Now(), and it gives a time format that Postgresql don't really understand, e.g. 2017-12-12 00:58:26.9589451 +0800 +08 m=+1406.914186601

But if I try to use loginTime := time.Now().Format(time.RFC3339), and the compiler complain that loginTime is a string, and I need it to be time.time type.

May I know how to handle this?

Sorry for asking newbie question, new to both Golang and Postgres. :)

In addition to what the answers recommend, you can also convert a valid timestamp string into a timestamp or timestamptz in the postgres query like so '2006-01-02T15:04:05Z07:00'::timestamp / '2006-01-02T15:04:05Z07:00'::timestamptz, works also with placeholders $2::timestamp... Just make sure the timestamp string has a format that is accepted by postgres, the one in your example is not. — mkopriva
– mkopriva, Commented Dec 11, 2017 at 19:44
this might help postgresql.org/docs/9.1/static/datatype-datetime.html — stevenferrer
– stevenferrer, Commented Dec 12, 2017 at 2:18

maerics · Accepted Answer · 2017-12-11 21:46:07Z

25

Instead of manually building the query string you should use sql#DB.Exec(...) with placeholder parameters so that the database driver properly escapes the values for you. Doing it this way is a best-practice, especially to avoid security errors such as SQL injection.

email, loginTime := "[email protected]", time.Now()
result, err := db.Exec("INSERT INTO UserAccount VALUES ($1, $2)", email, loginTime)
if err != nil {
  panic(err)
}

edited Dec 11, 2017 at 21:46

answered Dec 11, 2017 at 18:09

maerics

157k47 gold badges277 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kaedys Over a year ago

Note that Postgres does not support the ? placeholder. You must use $1 and $2, or use something like sqlx's Rebind() function.

David Budworth · Accepted Answer · 2017-12-11 18:08:18Z

9

The pq driver (which I assume you are using) automatically converts time.Time instances correctly for you.

so, you can do:

db.Exec(`INSERT INTO "UserAccount" ("email", "login_time") VALUES ($1, $2)`,"[email protected]",time.Now())

answered Dec 11, 2017 at 18:08

David Budworth

11.7k1 gold badge38 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to store Golang time.time in Postgresql timestamp?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related