CODEWITHSUNDEEP
postgresqlgo
Ryan Sam
Ryan Sam

Reputation: 3007

How should I assign Postgres date type to a variable

I'm inserting expiration dates into Postgres in a column which has this schema:
exp DATE NOT NULL

The way I'm creating these dates in Golang is with the "time" library that uses a Time data type (Not sure if it's underlying type is a string, the go docs didn't really specify see here).

Here's how I'm doing the insertion with the pgx library 

expirationDate := time.Unix(expiresAt, 0)
sql := `
  insert into items 
  (sub, uuid, exp) 
  VALUES ($1, $2, $3)
`
_, err := connection.Exec(
  context.Background(),
  sql,
  subject,
  id,
  expirationDate,
)

Somewhere else in my code expiresAt is made like this:

days := 1 * 24 * time.Hour
expirationTime := time.Now().Add(days)
expirationTime.Unix() // this is what's being passed in my function (expiresAt int64) that does the DB insertion

The problem is trying to get that date back out of Postgres.
When I look at it in pgAdmin I see it's being stored as yyyy-mm-dd so I assumed maybe I could assign it to a string variable so I tried this:

type RevokedRefreshToken struct {
  OldUUID           string
  OldExpirationDate string
}
var revokedRefreshToken RevokedRefreshToken
err := connection.QueryRow(context.Background(), "select uuid, exp from items where sub = $1", subject).
Scan(&revokedRefreshToken.OldUUID, &revokedRefreshToken.OldExpirationDate)

I also tried:
- int64 type
- Time type
- not passing it using its memory address (without the & in Scan())

I still can't figure out the data type I'm suppose to assign this DB value to.
Here is the error I'm getting that I think is from pgx: can't scan into dest[1]: unable to assign to *string

What data type do I need to be using to assign this DB value of PostgreSQL date type

Upvotes: 2

Views: 5813

Answers (1)

blami
blami

Reputation: 7431

If I remember correctly pgx maps DATE to time.Time. To scan you either need a pointer to the pointer or use pgx's pgtype.Date datatype (I am used to these so I always use them):

var dt pgtype.Date
err := conn.QueryRow("select exp from items where sub = $1", 1).Scan(&dt)

pgtype.Date has then field called Time which is of type time.Time so you should be able to work with it normally. See documentation.

Upvotes: 5

Related Questions