Datetimes from SQL Server read as time.local regardless of timezone used by Database

[GoogleCodeExporter]

When reading from SQL Server Datetime columns the date is always returned as a 
local time, when the database server stores all data in UTC (or some other 
timezone) this is a problem because read dates then have to be reconstructed to 
set the database timezone.

I think this can be traced to the column reading where time.Local is fed to the 
time.Date constructor. e.g.

        case api.SQL_C_TYPE_TIMESTAMP:
                t := (*api.SQL_TIMESTAMP_STRUCT)(p)
                r := time.Date(int(t.Year), time.Month(t.Month), int(t.Day),
                        int(t.Hour), int(t.Minute), int(t.Second), int(t.Fraction),
                        time.Local)

Original issue reported on code.google.com by ispa...@mdsol.com on 1 Apr 2014 at 6:18

[GoogleCodeExporter]

I can see your problem, but I don't think MS SQL Server stores "timezone" 
information anywhere in "datetime" or "datetime2" fields. So I don't see how 
odbc driver can "recover" that information from somewhere. If you do store 
"timezone" information somewhere (even by assuming that everything is in UTC or 
any other specific timezone), then it becomes your responsibility to handle 
that properly.

As to using time.Local when constructing dates, I had to come-up with a 
reasonable default. I made it so MS SQL Server gettime returns my current date 
and time. To demonstrate, this test:

# hg diff
diff -r 79ae99114308 mssql_test.go
--- a/mssql_test.go     Fri Feb 07 12:39:51 2014 +1100
+++ b/mssql_test.go     Wed Apr 02 10:32:02 2014 +1100
@@ -1218,3 +1218,18 @@
                t.Fatal("comparison fails")
        }
 }
+
+func TestMSSQLALEX(t *testing.T) {
+       db, sc, err := mssqlConnect()
+       if err != nil {
+               t.Fatal(err)
+       }
+       defer closeDB(t, db, sc, sc)
+
+       var dt time.Time
+       err = db.QueryRow("select getdate()").Scan(&dt)
+       if err != nil {
+               t.Fatal(err)
+       }
+       t.Logf("%v", dt)
+}

and I see this output:

# go test -mssrv=aaa -msdb=golang -msuser=gopher -mspass=password -v -run=ALEX
=== RUN TestMSSQLALEX
--- PASS: TestMSSQLALEX (0.01 seconds)
        mssql_test.go:1234: 2014-04-02 10:32:05.747 +1100 EST
PASS
ok      code.google.com/p/odbc  0.010s

The time on this output is the same as my computer clock.

If you have alternative proposal, I am listening. Thank you.

Alex

Original comment by alex.bra...@gmail.com on 2 Apr 2014 at 1:12

• Changed state: WaitingForReply

[GoogleCodeExporter]

Hi Alex, yes I agree that you've selected a sensible default. I think this is 
more of an inconvenience in my specific situation than a real issue but it 
would be nice to have a way to provide a default timezone to the connection so 
that:

              r := time.Date(int(t.Year), time.Month(t.Month), int(t.Day),
                        int(t.Hour), int(t.Minute), int(t.Second), int(t.Fraction),
                        conn.DefaultTimeZone) 

But the connection doesn't get passed to the column so that is not simple. 

So thanks for the reply, OK by me to close this issue.  Thanks for this library!

Original comment by ispa...@mdsol.com on 2 Apr 2014 at 1:15

[GoogleCodeExporter]

You can do something like that

diff --git a/mssql_test.go b/mssql_test.go
--- a/mssql_test.go
+++ b/mssql_test.go
@@ -1218,3 +1218,35 @@
        t.Fatal("comparison fails")
    }
 }
+
+type UTCTime time.Time
+
+// Scan implements the Scanner interface.
+func (utc *UTCTime) Scan(value interface{}) error {
+   t, istime := value.(time.Time)
+   if !istime {
+       return errors.New("Not a time.")
+   }
+   newt := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), 
t.Second(), t.Nanosecond(), time.UTC)
+   *utc = UTCTime(newt)
+   return nil
+}
+
+func (utc UTCTime) String() string {
+   return time.Time(utc).String()
+}
+
+func TestMSSQLALEX(t *testing.T) {
+   db, sc, err := mssqlConnect()
+   if err != nil {
+       t.Fatal(err)
+   }
+   defer closeDB(t, db, sc, sc)
+
+   var dt UTCTime
+   err = db.QueryRow("select getdate()").Scan(&dt)
+   if err != nil {
+       t.Fatal(err)
+   }
+   t.Logf("dt=%v", dt)
+}

to change timezone under the covers. Maybe this is more "convenient" for you.

Alternatively (since you use recent version of MS SQL Server), you can use 
datetimeoffset field. I haven't used it myself, and code.google.com/p/odbc 
package does not support it. I might implement it one day, but I don't have 
time to do it now.

Alex

Original comment by alex.bra...@gmail.com on 4 Apr 2014 at 12:45

• Changed state: WontFix