storing driver.Value type string into type *time.Time

[volker-raschek]

Hello all,
I am trying to rebuild an application with the RETURNING clause introduced in version 3.35.

https://www.sqlite.org/lang_returning.html

However, I always get an error that it does not match the data types unsupported Scan, storing driver.Value type string into type *time.Time.

I have reproduced the whole thing in the following projects and would like you to have a look at it to tell me if it is a bug of mine or if the library can't handle it.

https://github.com/volker-raschek/go-sqlite3-time

[rittneje]

SQLite3 (the C library) does not have a data type for date/time specifically. https://www.sqlite.org/datatype3.html#date_and_time_datatype

Sometimes this wrapper library is able to hide that particular fact, but only if you are directly selecting a column declared as DATE, DATETIME or TIMESTAMP. This behavior is not documented, and it has led to confusion multiple times due to its seeming inconsistency.

I don't know the specifics of how RETURNING has been implemented, but I would guess that returning a column doesn't support fetching its declared type. Whether this is a bug or intentional behavior on SQLite3's part is unclear.

For now, your best bet is to scan into a string, and then parse that into a time.Time. You can make a custom type that implements sql.Scanner to do so.

[rittneje]

Seems this is a known issue that is unlikely to ever be fixed. https://sqlite.org/forum/forumpost/725537a489a04d1e?t=h

@mattn Given this sort of issue seems to come up from time to time, it might be helpful to (1) add a typedef to this library that implements sql.Scanner and driver.Valuer in order to consistently convert between time.Time and a SQLite3-formatted string; and (2) add a flag to the DSN to disable the current automatic timestamp parsing logic when it is not expected or desired (e.g., #748).

[bartgrantham]

I've only briefly looked at the source, but it appears to use time.RFC3339Nano, however I guess this code isn't used for scanning? When I store timestamps they come out as 2021-12-01 22:59:36.05830699+00:00, which is not parsable as a time.RFC3339Nano (2006-01-02T15:04:05.999999999Z07:00).

This seems like a straightforward thing to resolve. The serialization/deserialization of time.Time should be symmetrical, it's not clear why it currently isn't.

[rittneje]

On the way into the database, it converts time.Time to a string using the format string "2006-01-02 15:04:05.999999999-07:00". I do not know why this particular format was chosen, but that is why you are getting "+00:00" instead of "Z". The reason for the conversion to string is that SQLite itself doesn't have a type for timestamps.

go-sqlite3/sqlite3.go

Line 1893 in 8543684

b := []byte(v.Format(SQLiteTimestampFormats[0]))

On the way out of the database, if the column type is date/datetime/timestamp, it will try parsing it with various format strings, the first of which is also "2006-01-02 15:04:05.999999999-07:00".

go-sqlite3/sqlite3.go

Lines 2166 to 2174 in 8543684

	case columnTimestamp, columnDatetime, columnDate:
	var t time.Time
	s = strings.TrimSuffix(s, "Z")
	for _, format := range SQLiteTimestampFormats {
	if timeVal, err = time.ParseInLocation(format, s, time.UTC); err == nil {
	t = timeVal
	break
	}
	}

So it will be symmetrical, if the column in question has the proper type declaration.