Repository: mattn/go-sqlite3 (c)

Issue:
Large Unix times are not retrieved correctly
My application stores dates as Unix times, integers that represent the number of seconds since January 1st, 1970. This works well in most cases but I noticed a spurious date -- August 30th, 1754 -- occasionally being returned in my Go queries. After some investigation I was able to trace this behaviour to go-sqlite3's handling of Unix times.

When writing to the database, I convert Time values to Unix times with the time.Unix() method. When reading from the database, go-sqlite3 handles the conversion from Unix time back to Time. However this can fail (silently) with large Unix times. For example, if you write a zero Time value to the database as an integer and then query it, you get the aforementioned date August 30th, 1754, even though the Unix time in the database is correct.

Calling time.Time{}.Unix() returns a large negative integer. To convert this integer to a Time value, go-sqlite3 first [converts it to nanoseconds](https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go#L965) by multiplying by 1e9. This causes an integer overflow. It then passes the bad result to the time.Unix() function, resulting in an incorrect Time value. This could be avoided by passing the Unix time directly to the Unix() function. Converting to nanoseconds is unnecessary and increases the chance of an overflow. See this [code snippet](https://play.golang.org/p/-RVHH-W-LB) for an illustration.

Any sufficiently large Unix time will overflow when multiplied by a billion. According to my tests, go-sqlite3 will hit problems with dates pre-1677 or post-2262. These are edge cases for sure but the fix is trivial. I can submit a PR if there's any interest.

Pull request description:
Don't convert Unix times to nanoseconds when querying datetime fields…
…. Fixes #430.

Patch:
diff --git a/sqlite3.go b/sqlite3.go
index b34c3a5..d3a6407 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -961,10 +961,11 @@ func (rc *SQLiteRows) Next(dest []driver.Value) error {
 				// 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 {
-					val *= int64(time.Second) // convert sec to nsec
+					t = time.Unix(val, 0)
 				}
-				t = time.Unix(0, val).UTC()
+				t = t.UTC()
 				if rc.s.c.loc != nil {
 					t = t.In(rc.s.c.loc)
 				}


Test patch:
diff --git a/sqlite3_test.go b/sqlite3_test.go
index 03b678d..e563479 100644
--- a/sqlite3_test.go
+++ b/sqlite3_test.go
@@ -403,6 +403,7 @@ func TestTimestamp(t *testing.T) {
 	}{
 		{"nonsense", time.Time{}},
 		{"0000-00-00 00:00:00", time.Time{}},
+		{time.Time{}.Unix(), time.Time{}},
 		{timestamp1, timestamp1},
 		{timestamp2.Unix(), timestamp2.Truncate(time.Second)},
 		{timestamp2.UnixNano() / int64(time.Millisecond), timestamp2.Truncate(time.Millisecond)},