Skip to content

Commit 4c5c4e5

Browse files
mattnmattn
committed
Add loc=XXX parameters to handle timezone
1 parent da2bf8a commit 4c5c4e5

2 files changed

Lines changed: 109 additions & 8 deletions

File tree

‎sqlite3.go‎

Lines changed: 44 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,7 @@ import (
6565
"errors"
6666
"fmt"
6767
"io"
68+
"net/url"
6869
"runtime"
6970
"strconv"
7071
"strings"
@@ -107,7 +108,8 @@ type SQLiteDriver struct {
107108

108109
// Conn struct.
109110
type SQLiteConn struct {
110-
db *C.sqlite3
111+
db *C.sqlite3
112+
loc *time.Location
111113
}
112114

113115
// Tx struct.
@@ -256,11 +258,31 @@ func errorString(err Error) string {
256258
// file:test.db?cache=shared&mode=memory
257259
// :memory:
258260
// file::memory:
261+
// go-sqlite handle especially query parameters.
262+
// loc=XXX
263+
// Specify location of time format. It's possible to specify "auto".
259264
func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
260265
if C.sqlite3_threadsafe() == 0 {
261266
return nil, errors.New("sqlite library was not compiled for thread-safe operation")
262267
}
263268

269+
var loc *time.Location
270+
if u, err := url.Parse(dsn); err == nil {
271+
for k, v := range u.Query() {
272+
switch k {
273+
case "loc":
274+
if len(v) > 0 {
275+
if v[0] == "auto" {
276+
v[0] = time.Local.String()
277+
}
278+
if loc, err = time.LoadLocation(v[0]); err != nil {
279+
return nil, fmt.Errorf("Invalid loc: %v: %v", v[0], err)
280+
}
281+
}
282+
}
283+
}
284+
}
285+
264286
var db *C.sqlite3
265287
name := C.CString(dsn)
266288
defer C.free(unsafe.Pointer(name))
@@ -281,7 +303,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
281303
return nil, Error{Code: ErrNo(rv)}
282304
}
283305

284-
conn := &SQLiteConn{db}
306+
conn := &SQLiteConn{db: db, loc: loc}
285307

286308
if len(d.Extensions) > 0 {
287309
rv = C.sqlite3_enable_load_extension(db, 1)
@@ -401,8 +423,13 @@ func (s *SQLiteStmt) bind(args []driver.Value) error {
401423
}
402424
rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(p), C.int(len(v)))
403425
case time.Time:
404-
b := []byte(v.UTC().Format(SQLiteTimestampFormats[0]))
405-
rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
426+
if s.c.loc != nil {
427+
b := []byte(v.In(s.c.loc).Format(SQLiteTimestampFormats[0]))
428+
rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
429+
} else {
430+
b := []byte(v.UTC().Format(SQLiteTimestampFormats[0]))
431+
rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
432+
}
406433
}
407434
if rv != C.SQLITE_OK {
408435
return s.c.lastError()
@@ -545,10 +572,19 @@ func (rc *SQLiteRows) Next(dest []driver.Value) error {
545572

546573
switch rc.decltype[i] {
547574
case "timestamp", "datetime", "date":
548-
for _, format := range SQLiteTimestampFormats {
549-
if timeVal, err = time.ParseInLocation(format, s, time.UTC); err == nil {
550-
dest[i] = timeVal.Local()
551-
break
575+
if rc.s.c.loc != nil {
576+
for _, format := range SQLiteTimestampFormats {
577+
if timeVal, err = time.ParseInLocation(format, s, rc.s.c.loc); err == nil {
578+
dest[i] = timeVal
579+
break
580+
}
581+
}
582+
} else {
583+
for _, format := range SQLiteTimestampFormats {
584+
if timeVal, err = time.ParseInLocation(format, s, time.UTC); err == nil {
585+
dest[i] = timeVal
586+
break
587+
}
552588
}
553589
}
554590
if err != nil {

‎sqlite3_test.go‎

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -744,6 +744,71 @@ func TestStress(t *testing.T) {
744744
}
745745
}
746746

747+
func TestDateTimeLocal(t *testing.T) {
748+
zone := "Asia/Tokyo"
749+
z, err := time.LoadLocation(zone)
750+
if err != nil {
751+
t.Skip("Failed to load timezon:", err)
752+
}
753+
tempFilename := TempFilename()
754+
db, err := sql.Open("sqlite3", "file:///"+tempFilename+"?loc="+zone)
755+
if err != nil {
756+
t.Fatal("Failed to open database:", err)
757+
}
758+
db.Exec("CREATE TABLE foo (id datetime);")
759+
db.Exec("INSERT INTO foo VALUES('2015-03-05 15:16:17');")
760+
761+
row := db.QueryRow("select * from foo")
762+
var d time.Time
763+
err = row.Scan(&d)
764+
if err != nil {
765+
t.Fatal("Failed to scan datetime:", err)
766+
}
767+
if d.Local().Hour() != 15 {
768+
t.Fatal("Result should have timezone", d)
769+
}
770+
db.Close()
771+
772+
db, err = sql.Open("sqlite3", "file:///"+tempFilename)
773+
if err != nil {
774+
t.Fatal("Failed to open database:", err)
775+
}
776+
777+
row = db.QueryRow("select * from foo")
778+
err = row.Scan(&d)
779+
if err != nil {
780+
t.Fatal("Failed to scan datetime:", err)
781+
}
782+
if d.In(z).Hour() == 15 {
783+
t.Fatalf("Result should not have timezone %v", zone)
784+
}
785+
786+
_, err = db.Exec("DELETE FROM foo")
787+
if err != nil {
788+
t.Fatal("Failed to delete table:", err)
789+
}
790+
dt, err := time.Parse("2006/1/2 15/4/5 -0700 MST", "2015/3/5 15/16/17 +0900 JST")
791+
if err != nil {
792+
t.Fatal("Failed to parse datetime:", err)
793+
}
794+
db.Exec("INSERT INTO foo VALUES(?);", dt)
795+
796+
db.Close()
797+
db, err = sql.Open("sqlite3", "file:///"+tempFilename+"?loc="+zone)
798+
if err != nil {
799+
t.Fatal("Failed to open database:", err)
800+
}
801+
802+
row = db.QueryRow("select * from foo")
803+
err = row.Scan(&d)
804+
if err != nil {
805+
t.Fatal("Failed to scan datetime:", err)
806+
}
807+
if d.Hour() == 15 {
808+
t.Fatalf("Result should have timezone %v", zone)
809+
}
810+
}
811+
747812
func TestVersion(t *testing.T) {
748813
s, n, id := Version()
749814
if s == "" || n == 0 || id == "" {

0 commit comments

Comments
 (0)