DATE, DATETIME, TIMESTAMP in SQLite are incorrectly typed as time.Time in Go

[braaar]

Version

1.27.0

What happened?

For DATE, DATETIME and TIMESTAMP columns in SQLite, SQLC generates time.Time fields in the corresponding Go struct.

When I attempt to read data from such columns from an SQLite database with the generated SQLC code, I get an Unsupported Scan error, since the actual type of the data is NUMERIC (can be int or float, in my case I store unix epoch timestamps so the actual values will be int64).

SQLite has no true datetime datatype. A column named DATE, DATETIME, TIMESTAMP or SLARTIBARTFAST in SQlite is going to end up being NUMERIC, due to SQLite's type affinity system. It would be advisable to change the SQLC generated types of such columns to be either int64, which would apply well to cases like mine where an integer is stored, or float64, which would apply well in cases where float values are stored in the database. I don't know if there is a Go type that corresponds well to SQLite's numeric, but I think realistically speaking dates are far more likely to be represented as ints in actual use (unix epoch), so the best compromise would be that.

Alternatively, removing the specific type mappings for timestamp could also be an acceptable solution, thus forcing users to make their own overrides.

Alternatively, one could write some kind of parser, but as far as I understand, this would not fall within the scope of SQLC.

I believe this is the relevant code that needs to be changed.

Relevant log output

ERROR An unexpected database error has occured error="sql: Scan error on column index 3, name \"my_date\": unsupported Scan, storing driver.Value type int64 into type *time.Time"

Database schema

CREATE TABLE
    my_table (
        my_date TIMESTAMP NOT NULL
    );

SQL queries

SELECT
    my_date
FROM
    my_table

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries:
      - "internal/storage/queries/*.sql"
    schema: "internal/storage/migrations"
    gen:
      go:
        package: "sql"
        out: "generated/sql"
        emit_interface: true

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[rishi-kulkarni]

I suspect this was done to keep compatibility with mattn's driver: mattn/go-sqlite3#748. It would be a breaking change to fix it at this point.