Helper functions for time.Time

[delaneyj]

I'd be nice to be able to have Get/Set of time.Time directly. Versions for rfc3339, int64 unix sec && int64 unix milliseconds it what i tend to use most. Are you open to PRs?

[zombiezen]

While I agree that storing times is a pretty common case (I use it a lot myself!), I don't want to introduce the surface area in this package. Reason being: SQLite notably doesn't have a time type, so the representation that applications use vary based on need. I would rather just expose the column access primitives, and then have applications transform the data into time.Time values.

I'm open to hearing concrete API proposals, but I don't want to raise hopes that I'm necessarily going to merge anything. After all, "no is temporary, yes is forever."

Notes:

• The built-in date time functions all operate at most with millisecond precision
• current_timestamp uses second precision
• I'm seeing that mattn/go-sqlite3 has many issues with time conversions. This suggests to me that a simple approach doesn't match user expectations.

[delaneyj]

I want to argue but damn, you have thought it through. Maybe it could/should live at a sqlitex level, if at all.

[zombiezen]

I agree that sqlitex would be the right place for it, if such a function should be added. The strawman I've been thinking of is:

package sqlitex

// ColumnTime returns a query result as a time in UTC. The time must be
// in one of the formats 1-7 from https://sqlite.org/lang_datefunc.html#time_values.
// That is, one of:
//
//   YYYY-MM-DD
//   YYYY-MM-DD HH:MM
//   YYYY-MM-DD HH:MM:SS
//   YYYY-MM-DD HH:MM:SS.SSS
//   YYYY-MM-DDTHH:MM
//   YYYY-MM-DDTHH:MM:SS
//   YYYY-MM-DDTHH:MM:SS.SSS
//
// Column indices start at 0.
func ColumnTime(stmt *sqlite.Stmt, col int) time.Time

// GetTime returns a query result value for colName as a time.
func GetTime(stmt *sqlite.Stmt, colName string) time.Time

The part where I'm stuck is that this implies for symmetry that there should be a BindTime, but since there is no completely ubiquitous format, I don't think providing a function is as clear as callers writing:

stmt.BindText(1, timeValue.Format("2006-01-02 15:04:05"))

[delaneyj]

I was thinking of it being a little more complicated than that. So for example if you're using the Julian for float64 you can actually get down to the microsecond level if I recall correctly. The string version is easier give that saucing and loading as time.3339 works well already. I can totally add in my own version no problem it just seems like something that's very common at this level