sql type Time being converted to Golang time.Time, what could not work

[Skopjuk]

Version

1.25.0

What happened?

I have a table with a column typed TIME NOT NULL. I generated code for select statement from this table and received an error

"sql: Scan error on column index 2, name "time": unsupported Scan, storing driver.Value type []uint8 into type *time.Time".

parseTime = true, already, that's not a point. As far I figured out at the moment the problem that sqlc generated struct for this table and for the column typed TIME used Golang time.Time type. But Golang can not convert to time.Time the structure without a date, so that's the problem. Maybe it would be betted to convert TIME to time.Time directly.

code sqlc generated:

type VideoTiming struct {
 ID          string
 VideoID     string
 Time        time.Time
 Description sql.NullString
 CreatedAt   time.Time
 DeletedAt   sql.NullTime
}

func (q *Queries) GetVideoTimings(ctx context.Context, videoID string) ([]VideoTiming, error) {
 rows, err := q.db.QueryContext(ctx, getVideoTimings, videoID)
 if err != nil {
  return nil, err
 }
 defer rows.Close()
 var items []VideoTiming
 for rows.Next() {
  var i VideoTiming
  if err := rows.Scan(
   &i.ID,
   &i.VideoID,
   &i.Time,
   &i.Description,
   &i.CreatedAt,
   &i.DeletedAt,
  ); err != nil {
   return nil, err
  }
  items = append(items, i)
 }
 if err := rows.Close(); err != nil {
  return nil, err
 }
 if err := rows.Err(); err != nil {
  return nil, err
 }
 return items, nil
}

Relevant log output

No response

Database schema

CREATE TABLE IF NOT EXISTS timings (
    id VARCHAR(36) PRIMARY KEY,
    video_id VARCHAR(36) NOT NULL,
    timeMark INT NOT NULL,
    description VARCHAR(500),
    created_at       TIMESTAMP NOT NULL,
    deleted_at       TIMESTAMP
);

SQL queries

-- name: GetVideoTimings :many
SELECT * FROM video_timings WHERE video_id = ? AND deleted_at IS NULL;

Configuration

version: "2"
cloud:
    organization: ""
    project: ""
    hostname: ""
sql:
    - engine: "mysql"
      queries: "/queries/"
      schema: "/migrations/"
      gen:
          go:
              package: "repository"
              out: "repository"
overrides:
    go: null
plugins: []
rules: []
options: {}

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[o-ga09]

I faced this problem .

the version of my environment : v1.27.0

[Th3rm1t0]

I also encountered the same issue in v1.27.0. As a workaround, I modified the scan process to handle TIME type columns as strings using Overriding Types. This fixed the issue for my project, but it would be great if this could work out-of-the-box without requiring such an override.

It seems that currently, the generated code scans all "date", "timestamp", "datetime", and "time" columns as time.Time. However, since TIME type columns are not parsed as time.Time even when parseTime=true is set, I suspect the generated code may not handle TIME type columns correctly as is.

https://github.com/sqlc-dev/sqlc/blob/da092010d5beb4281f6a7f0f197ac55524b10362/internal/codegen/golang/mysql_type.go#L98-L102

Just wanted to share my experience in case it helps! 😊

[Skopjuk]

Thank you for sharing, I guess it will help those who will meet this problem in the future. I used the same strategy, it is not the same as I expected it to work tho, but ok for out purpose.