sqlite3 doesn't have datetime/timestamp types

[ghost]

https://github.com/mattn/go-sqlite3/blob/master/doc.go

+------------------------------+
|go        | sqlite3           |
|----------|-------------------|
|nil       | null              |
|int       | integer           |
|int64     | integer           |
|float64   | float             |
|bool      | integer           |
|[]byte    | blob              |
|string    | text              |
|time.Time | timestamp/datetime|
+------------------------------+

https://www.sqlite.org/draft/datatype3.html
...
2.2. Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
...

[rittneje]

You are correct that SQLite doesn't really support timestamps as such. This library follows a best effort approach for dealing with them, but it is by no means perfect.

When reading values out of the database, if the declared type of the column is DATE, DATETIME, or TIMESTAMP and the storage class of the actual value is INTEGER or TEXT, it will be read out into a time.Time.

go-sqlite3/sqlite3.go

Lines 2005 to 2022 in 4396a38

	case C.SQLITE_INTEGER:
	val := int64(C.sqlite3_column_int64(rc.s.s, C.int(i)))
	switch rc.decltype[i] {
	case columnTimestamp, columnDatetime, columnDate:
	var t time.Time
	// Assume a millisecond unix timestamp if it's 13 digits -- too
	// large to be a reasonable timestamp in seconds.
	if val > 1e12 || val < -1e12 {
	val *= int64(time.Millisecond) // convert ms to nsec
	t = time.Unix(0, val)
	} else {
	t = time.Unix(val, 0)
	}
	t = t.UTC()
	if rc.s.c.loc != nil {
	t = t.In(rc.s.c.loc)
	}
	dest[i] = t

go-sqlite3/sqlite3.go

Lines 2040 to 2064 in 4396a38

	case C.SQLITE_TEXT:
	var err error
	var timeVal time.Time
	n := int(C.sqlite3_column_bytes(rc.s.s, C.int(i)))
	s := C.GoStringN((*C.char)(unsafe.Pointer(C.sqlite3_column_text(rc.s.s, C.int(i)))), C.int(n))
	switch rc.decltype[i] {
	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
	}
	}
	if err != nil {
	// The column is a time value, so return the zero time on parse failure.
	t = time.Time{}
	}
	if rc.s.c.loc != nil {
	t = t.In(rc.s.c.loc)
	}
	dest[i] = t

When binding parameters to a query, if the parameter is a time.Time, it is actually formatted and bound as a TEXT value.

go-sqlite3/sqlite3.go

Lines 1815 to 1817 in 4396a38

	case time.Time:
	b := []byte(v.Format(SQLiteTimestampFormats[0]))
	rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))

[tebruno99]

When reading CoreData sqlite databases a lot of apps tend to write the CFAbsoluteTime which results in a TIMESTAMP field that cannot be converted into a time.Time correctly. The contents being "626839883.818818" for example.

Source: https://www.epochconverter.com/coredata

This has left me in a position where I don't seem to be able to get the value out of the DB correctly.

1,"626839883.818818"
2,"626839883.818818"
3,"428350966"
4,"428350966"
5,"626839883.818818"

var PK int64
var mactime string
err = rows.Scan(&PK,&mactime)

This results in mixed values for the string, and failure if you use time.Time
Result: (fmt.Printf("%d %s", PK, mactime)

5 6.28350966099928e+08
1 6.26839883818818e+08
2 6.26839883818818e+08
3 1983-07-29T18:22:46Z
4 1983-07-29T18:15:00Z

Do you have any suggestion on how I can get an consistant integer or float out of these databases with go-sqlite3 ?

[rittneje]

@tebruno99 Here is one possible solution to your problem.

First, you will want to explicitly cast the column in question to FLOAT. This will circumvent the logic in this library that tries to parse it as a (normal) timestamp. Then scan the column into a Go float64 and convert to a time.Time as per your link. For example:

coreEpoch := time.Date(2001, time.January, 1, 0, 0, 0, 0, time.UTC)

rows, err := db.Query(`SELECT EntryID, CAST(Timestamp AS FLOAT) FROM Entries`)
if err != nil {
	...
}
defer rows.Close()

for rows.Next() {
	var id uint32
	var coreTimestamp float64

	if err := rows.Scan(&id, &coreTimestamp); err != nil {
		...
	}
	timestamp := coreEpoch.Add(time.Duration(coreTimestamp * float64(time.Second)))

	fmt.Printf("%d: %s\n", id, timestamp)
}

if err := rows.Err(); err != nil {
	...
}

Output from your sample data:

1: 2020-11-12 02:11:23.81881792 +0000 UTC
2: 2020-11-12 02:11:23.81881792 +0000 UTC
3: 2014-07-29 18:22:46 +0000 UTC
4: 2014-07-29 18:22:46 +0000 UTC
5: 2020-11-12 02:11:23.81881792 +0000 UTC

[tebruno99]

@rittneje Hey Thanks for the reply! This was the first thing I thought I tried but I didn't CAST in the query when I was trying to figure it out. Thanks for the tip!

[tebruno99]

It sure would be cool if it could try and detect what it was trying to scan into (passing in a float) to determine what type to convert instead of a mix of column type hints.

[rittneje]

Unfortunately, this is an artifact of Go's driver API. The driver implementation does not get to see what you are trying to scan into. Instead, the driver is supposed to read out a row of data, and the sql package itself does the conversion. This, combined with SQLite's lack of a first-class timestamp type, leads to these sorts of discrepancies. https://golang.org/pkg/database/sql/driver/#Rows