Age() function not working with time.Time-CodePudding

Trying to use age() function from postgresql with DateOfBirth

type Person struct {
  ID int
  DOB time.Time
}

query := `SELECT id, AGE(dateofbirth) from person WHERE id = $1`

But got an error

Unsupported Scan, storing driver.Value type []uint8 into type *time.Time

The age() function work if I just pass the DOB as string

type Person struct {
  ID int
  DOB string
}

But I want to check the validity of DOB so I use time.Parse("2006-01-02", DOB)

func createPerson(w http.ResponseWriter, r *http.Request) {
  err := r.ParseForm()
  if err != nil {
        log.Fatal(err)
  }
  dob := r.PostForm.Get("dob")
  birthday, _ := time.Parse("2006-01-02", dob)

  err = models.person.Insert(birthday)
  if err != nil {
        log.Fatal("Server Error: ", err)
        return
  }
  http.Redirect(w, r, "/", http.StatusSeeOther)
}

func getPerson(w http.ResponseWriter, r *http.Request) {
  id, err := strconv.Atoi(chi.URLParam(r, "id"))
  if err != nil {
    log.Fatal("Not found", err)
    return
  }
  person, err = models.person.Get(id)
  if err != nil {
      log.Fatal("Server Error: ", err)
      return
  }
  render.HTML(w, http.StatusOK, "person.html", person)
}

CodePudding user response：

Your database driver is responsible for transforming the incoming data from the database into Go data types. I'll assume you're using pq, which has the following rules about data types:

This package returns the following types for values from the PostgreSQL backend:

integer types smallint, integer, and bigint are returned as int64

floating-point types real and double precision are returned as float64

character types char, varchar, and text are returned as string

temporal types date, time, timetz, timestamp, and timestamptz are returned as time.Time

the boolean type is returned as bool

the bytea type is returned as []byte

All other types are returned directly from the backend as []byte values in text format.

In PostgreSQL, the AGE function returns data type interval. This is not one of the directly supported data types, so pq processes it as []byte. That is why it works when you make DOB a string. Conversion between []byte and string in Go is trivial, so the driver is able to fill in the value.

The driver fails to fill the []byte into a time.Time value because it doesn't know what conversion routine to use. Actually, there is no direct conversion routine because interval represents a duration of time, while time.Time represents an instance of time. interval is more analogous to a time.Duration value. Still, the driver doesn't support any automatic conversion from interval to any type, and I'm not aware of a way to add a new conversion. You would have to implement the conversion yourself after fetching the data from the database.

You could also switch to the pgx driver, which supports an Interval type.