Inconsistent parsing of DATETIME type, causing errors

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:

• Fails if any created_at value in the table has happens to be an integer: SELECT created_at FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1 when I call Scan(&float64Val)
• Always works (force real with *1.0): SELECT created_at*1.0 FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1

Create a table like so.

		CREATE TABLE IF NOT EXISTS property_history (
			id INTEGER PRIMARY KEY,
			name TEXT NOT NULL,
			text_value TEXT,
			created_at DATETIME,
			updated_at DATETIME
		);

		CREATE INDEX IF NOT EXISTS property_history_name_created_at ON property_history (name, created_at);

		CREATE TRIGGER IF NOT EXISTS insert_property_history_created_at
		AFTER INSERT ON property_history
		BEGIN
			UPDATE property_history SET created_at =unixepoch('subsec') WHERE id = NEW.id;
		END;

Then run this test case (db.sqldb is a sql.DB created with this driver):

func TestTimestampRoundingAndLatestPropHistory(t *testing.T) {
	db := testBuildTestDb(t)
	defer db.Close()

	// insert a row into table
	_, err := db.sqldb.Exec(`
		INSERT INTO property_history (name, type, text_value, sample_type)
		VALUES ('test', ?, 'val', 1)
	`, DBPropertyTypeString)
	if err != nil {
		t.Fatal(err)
	}

        // Running `SELECT created_at FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1`
	ct, err := db.latestPropertyHistoryTime("test")
	if err != nil {
                // this fails about 1 in 1000 times, but usually passes. We force a 100% failure below.
		t.Fatal(err)
	}
	if ct == nil {
		t.Fatal("LatestPropHistoryTime returned nil")
	}
	if math.Abs(time.Since(*ct).Seconds()) > 0.01 {
		t.Fatal("LatestPropHistoryTime returned wrong time")
	}

	// update the row to exact time, no milliseconds
	// This previously caused a scan error (even when the row returned still was fractional)
	_, err = db.sqldb.Exec(`
		UPDATE property_history SET created_at = 1710791550
		WHERE name = 'test'
	`)
	if err != nil {
		t.Fatal(err)
	}

        // Running `SELECT created_at FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1`
	ct, err = db.latestPropertyHistoryTime("test")
	if err != nil {
		t.Fatal(err) // **fails here with error above**
	}
	if ct == nil {
		t.Fatal("LatestPropHistoryTime returned nil")
	}
	if math.Abs(float64(ct.Unix())-1710791550.0) > 0.1 {
		t.Fatal("LatestPropHistoryTime returned wrong time")
	}
}

The function call used in test above:

const latestPropHistoryTimeByNameQuery = `SELECT created_at FROM property_history WHERE name = ? ORDER BY created_at DESC LIMIT 1`

func (db *DB) latestPropertyHistoryTime(name string) (*time.Time, error) {
	var epochTime float64
	err := db.sqldb.
		QueryRow(latestPropHistoryTimeByNameQuery, name).
		Scan(&epochTime)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	_, fractionalSeconds := math.Modf(epochTime)
	nanoseconds := int64(fractionalSeconds * 1_000_000_000)
	time := time.Unix(int64(epochTime), nanoseconds)
	return &time, nil
}

Start by running TestTimestampRoundingAndLatestPropHistory and tracing latestPropertyHistoryTime through the driver's DATETIME conversion and database/sql Scan path. Compare fractional and exact-second created_at values; done means the query returns a consistent timestamp representation without the intermittent scan error.