diff options
| -rw-r--r-- | README.md | 4 | ||||
| -rw-r--r-- | sqlite3.go | 22 | ||||
| -rw-r--r-- | sqlite3_test.go | 26 |
3 files changed, 38 insertions, 14 deletions
@@ -16,6 +16,10 @@ This package can be installed with the go get command: go get github.com/mattn/go-sqlite3 +_go-sqlite3_ is *cgo* package. +If you want to build your app using go-sqlite3, you need gcc. +However, if you install _go-sqlite3_ with `go install github.com/mattn/go-sqlite`, you don't need gcc to build your app anymore. + Documentation ------------- @@ -99,6 +99,10 @@ import ( // into the database. When parsing a string from a timestamp or // datetime column, the formats are tried in order. var SQLiteTimestampFormats = []string{ + // By default, store timestamps with whatever timezone they come with. + // When parsed, they will be returned with the same timezone. + "2006-01-02 15:04:05.999999999-07:00", + "2006-01-02T15:04:05.999999999-07:00", "2006-01-02 15:04:05.999999999", "2006-01-02T15:04:05.999999999", "2006-01-02 15:04:05", @@ -106,7 +110,6 @@ var SQLiteTimestampFormats = []string{ "2006-01-02 15:04", "2006-01-02T15:04", "2006-01-02", - "2006-01-02 15:04:05-07:00", } func init() { @@ -803,7 +806,7 @@ func (s *SQLiteStmt) bind(args []driver.Value) error { } rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(p), C.int(len(v))) case time.Time: - b := []byte(v.UTC().Format(SQLiteTimestampFormats[0])) + b := []byte(v.Format(SQLiteTimestampFormats[0])) rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b))) } if rv != C.SQLITE_OK { @@ -902,18 +905,15 @@ func (rc *SQLiteRows) Next(dest []driver.Value) error { val := int64(C.sqlite3_column_int64(rc.s.s, C.int(i))) switch rc.decltype[i] { case "timestamp", "datetime", "date": - unixTimestamp := strconv.FormatInt(val, 10) var t time.Time - if len(unixTimestamp) == 13 { - duration, err := time.ParseDuration(unixTimestamp + "ms") - if err != nil { - return fmt.Errorf("error parsing %s value %d, %s", rc.decltype[i], val, err) - } - epoch := time.Date(1970, 1, 1, 0, 0, 0, 0, time.UTC) - t = epoch.Add(duration) + // 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 } else { - t = time.Unix(val, 0) + val *= int64(time.Second) // convert sec to nsec } + t = time.Unix(0, val).UTC() if rc.s.c.loc != nil { t = t.In(rc.s.c.loc) } diff --git a/sqlite3_test.go b/sqlite3_test.go index 22b9c21..dfe1c43 100644 --- a/sqlite3_test.go +++ b/sqlite3_test.go @@ -347,6 +347,8 @@ func TestBooleanRoundtrip(t *testing.T) { } } +func timezone(t time.Time) string { return t.Format("-07:00") } + func TestTimestamp(t *testing.T) { tempFilename := TempFilename(t) db, err := sql.Open("sqlite3", tempFilename) @@ -365,6 +367,7 @@ func TestTimestamp(t *testing.T) { timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC) timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC) timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC) + tzTest := time.FixedZone("TEST", -9*3600-13*60) tests := []struct { value interface{} expected time.Time @@ -372,9 +375,9 @@ func TestTimestamp(t *testing.T) { {"nonsense", time.Time{}}, {"0000-00-00 00:00:00", time.Time{}}, {timestamp1, timestamp1}, - {timestamp1.Unix(), timestamp1}, - {timestamp1.UnixNano() / int64(time.Millisecond), timestamp1}, - {timestamp1.In(time.FixedZone("TEST", -7*3600)), timestamp1}, + {timestamp2.Unix(), timestamp2.Truncate(time.Second)}, + {timestamp2.UnixNano() / int64(time.Millisecond), timestamp2.Truncate(time.Millisecond)}, + {timestamp1.In(tzTest), timestamp1.In(tzTest)}, {timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1}, {timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1}, {timestamp1.Format("2006-01-02 15:04:05"), timestamp1}, @@ -382,6 +385,7 @@ func TestTimestamp(t *testing.T) { {timestamp2, timestamp2}, {"2006-01-02 15:04:05.123456789", timestamp2}, {"2006-01-02T15:04:05.123456789", timestamp2}, + {"2006-01-02T05:51:05.123456789-09:13", timestamp2.In(tzTest)}, {"2012-11-04", timestamp3}, {"2012-11-04 00:00", timestamp3}, {"2012-11-04 00:00:00", timestamp3}, @@ -389,6 +393,14 @@ func TestTimestamp(t *testing.T) { {"2012-11-04T00:00", timestamp3}, {"2012-11-04T00:00:00", timestamp3}, {"2012-11-04T00:00:00.000", timestamp3}, + {"2006-01-02T15:04:05.123456789Z", timestamp2}, + {"2012-11-04Z", timestamp3}, + {"2012-11-04 00:00Z", timestamp3}, + {"2012-11-04 00:00:00Z", timestamp3}, + {"2012-11-04 00:00:00.000Z", timestamp3}, + {"2012-11-04T00:00Z", timestamp3}, + {"2012-11-04T00:00:00Z", timestamp3}, + {"2012-11-04T00:00:00.000Z", timestamp3}, } for i := range tests { _, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value) @@ -423,6 +435,14 @@ func TestTimestamp(t *testing.T) { if !tests[id].expected.Equal(dt) { t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt) } + if timezone(tests[id].expected) != timezone(ts) { + t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value, + timezone(tests[id].expected), timezone(ts)) + } + if timezone(tests[id].expected) != timezone(dt) { + t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value, + timezone(tests[id].expected), timezone(dt)) + } } if seen != len(tests) { |