Columns defined as TIMESTAMP DEFAULT 0 scan to the year -4713

[john-floren-gravwell]

Consider the following code:

package main

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
	// _ "github.com/ncruces/go-sqlite3/driver"
	// _ "github.com/ncruces/go-sqlite3/embed"
	"log"
	"os"
	"path/filepath"
	"time"
)

func main() {
	dir, _ := os.MkdirTemp("", "foo")
	defer os.RemoveAll(dir)

	db, err := sql.Open("sqlite3", filepath.Join(dir, "test.db"))
	if err != nil {
		log.Fatal(err)
	}

	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS test ( x TEXT, ts TIMESTAMP DEFAULT 0 );`); err != nil {
		log.Fatal(err)
	}

	if _, err := db.Exec(`INSERT INTO test ( x ) VALUES ( 'foo' );`); err != nil {
		log.Fatal(err)
	}

	row := db.QueryRow(`SELECT * from test;`)

	var val string
	var t time.Time
	if err := row.Scan(&val, &t); err != nil {
		log.Fatal(err)
	}

	log.Println(t)
}

When run as shown, using the github.com/mattn/go-sqlite3 driver, my TIMESTAMP DEFAULT 0 column comes back as the Unix epoch date (jan 1 1970).

If I switch around the imports to use ncruces/go-sqlite3 instead, the TIMESTAMP column comes back as -4713-11-24 12:00:00 +0000 UTC, which makes encoding/json very unhappy among other things.

Is there a way to tell the library to treat a 0 value as the epoch zero, the way mattn does?

[ncruces]

This is explained in the driver documentation.

The default format used by the driver is auto, which is detailed in the SQLite documentation and the TimeFormat.Decode documentation; it's safe to use from at least 1980 through at least 2260, otherwise there is ambiguity.

The value 0 (zero) is one such case. Note that if you fire up the sqlite3 CLI, or open the SQLite3 Fiddle, and run the query SELECT datetime(0); the result is precisely -4713-11-24 12:00:00.

So this is not a bug, simply an incompatibility with mattn, and not SQLite; between mattn and SQLite, I'll follow SQLite.

That said, if you'd prefer to use Unix timestamps for everything, you can set _timefmt to unixepoch or unixepoch_frac (or unixepoch_nano, take your pick), as described in the driver documentation, for example:

sql.Open("sqlite3", "file:demo.db?_timefmt=unixepoch_frac")

[john-floren-gravwell]

Thank you, I had missed _timefmt in the documentation. Setting it to unixepoch_frac works great in combination with tables that look like this:

created_at TIMESTAMP DEFAULT (unixepoch('now', 'subsec')),
deleted_at TIMESTAMP DEFAULT 0,

[ncruces]

Glad that fixes it, hopefully this helps others!

I wonder if for SQLite a default of zero makes the most sense, compared to NULL - precisely because 0 for missing is ambiguous under auto. On SQLite because of dynamic typing, you don't end up gaining much in terms of storage with avoiding NULL vs. an integer.