Inconsistent parsing of DATETIME type, causing errors about go-sqlite3 HOT 7 OPEN

There is no datetime type in sqlite3. Everything is treated as text on sqlite3 for unknown types.
go-sqlite3 implements a special treatment that allows the text to be treated as a string or time.
If you use time.Time or string instead of float32, you will not have this problem. If you want to treat created_at as int64 or float64, you should declare the type of created_at as integer or real.

from go-sqlite3.

See https://www.sqlite.org/datatype3.html

from go-sqlite3.

Ah. I knew sqlite was mapping SQL-spec types to a smaller subset. I didn't realize DATETIME -> NUMERIC, and numeric prefers int to real. Looks like in this case, values close to round numbers went to ints.

Note: Scanning DATETIME into time.Time doesn't work. It would be ideal if it did. I only went the epochTime/float64 route when I saw the query returning float64. When a DATETIME (aka NUMERIC) column has a real (non int) value it errors with sql: Scan error on column index 0, name "created_at": unsupported Scan, storing driver.Value type float64 into type *time.Time

Can the time.Time be updated to accept both int and real values?

from go-sqlite3.

Seems like a case could be added in convert.go for src=float64, dest=time.Time?

from go-sqlite3.

You can make new type which can convert integer/real to time.Time.

func (v *MyDateTime) Scan(value interface{}) error {
    // ...
}

func (v *MyDateTime) Value() (driver.Value, error) {
    // ...
}

from go-sqlite3.

BTW, why you don't define epochTime as time.Time ?

from go-sqlite3.

why you don't define epochTime as time.Time ?

That's what I'd like to do, but it hits Scan error on column index 0, name "created_at": unsupported Scan, storing driver.Value type float64 into type *time.Time if any values are floating point.

I def could go the custom type route.

Since the driver support epoch integers, and the SQL DATETIME type maps to both ints and reals (and strings) in sqlite, it would be nice if reals were also supported by the driver as time.Time, like ints are. Correct me if I'm wrong, but I think this is all valid SQL, and only errors on this driver?

Let me know if you'd be open to a patch adding time.Time to float64 conversion in convert.go. I could see not wanting to break back compatibility. There's also the question of information loss: I don't think it will happen in practice often, but someone could add higher than nanosecond precision to a floating point field, in which case time.Time would be losing data.

from go-sqlite3.