New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Inconsistent parsing of DATETIME type, causing errors #1229
Comments
Detailed writeup here: mattn/go-sqlite3#1229 Issue was that if a time in fractional seconds happened to round to an exact second, scans would fail. I forced a REAL by adding *1.0, so consistently floating point Also a bit more migration robustness: don't expose the sql.DB object to others until migrate is run. Includes test that repos old SQL issue
There is no datetime type in sqlite3. Everything is treated as text on sqlite3 for unknown types. |
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 Can the time.Time be updated to accept both int and real values? |
Seems like a case could be added in |
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) {
// ...
} |
BTW, why you don't define epochTime as time.Time ? |
That's what I'd like to do, but it hits 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. |
I use DATETIME type, with sub second precision in my schema. A scan against this table sometimes errors with this error:
Scan error on column index 0, name "created_at": converting driver.Value type time.Time ("2024-03-05 20:36:02 +0000 UTC") to a float64: invalid syntax
This only happens if a created_at value in the table is perfectly round (no decimal place). Then it seems to try to scan to time.Time, while the values with fractions try to scan to float64. It's a rare repo since it doesn't happen unless the timestamp exactly rounds to an even second (1/1000 chance)
Interestingly this error occurs if any value in the table has a perfectly round value, not just the row being returned by "LIMIT 1". I would have though only the returned value makes it up to the golang parsing layer.
IMO it should alway return time.Time. I used float64 when I saw the scan return floats. However, inconsistent and rare is a problem.
Query:
SELECT created_at FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1
when I callScan(&float64Val)
*1.0
):SELECT created_at*1.0 FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1
Create a table like so.
Then run this test case (
db.sqldb
is a sql.DB created with this driver):The function call used in test above:
The text was updated successfully, but these errors were encountered: