Date & Timestamp Timezone

[greeny1000]

Hi, Great library, Thank you.

I have bumped up against an issue.
Using firebirdsql all date & timestamp values are returned as UTC When writing to DB, If all Go time.Time values are converted to UTC before passing to firebirdsql, then results are as expected.

When inserting dates & timestamp using a time.Time with a non UTC timezone problems can occur

---

In driver_test.go ,
func TestInsertTimestamp(t *testing.T) if change dt1 := time.Date(2015, 2, 9, 19, 25, 50, 740500000, time.UTC) to

loc, _ := time.LoadLocation("America/New_York")  //any non UTC location will do
dt1 := time.Date(2015, 2, 9, 19, 25, 50, 740500000, loc)

test will fail

---

Perhaps this could be solved by taking time=time.UTC() before writing to DB? ie. in utils.go (sorry i'm not set up to do a pr at the moment)

func _convert_date(t time.Time) []byte {
        t = t.UTC()
    i := int(t.Month()) + 9
    jy := t.Year() + (i / 12) - 1
    jm := i % 12
    c := jy / 100
    jy -= 100 * c
    j := (146097*c)/4 + (1461*jy)/4 + (153*jm+2)/5 + t.Day() - 678882
    return bint32_to_bytes(int32(j))
}

func _convert_time(t time.Time) []byte {
        t = t.UTC()
    v := (t.Hour()*3600+t.Minute()*60+t.Second())*10000 + t.Nanosecond()/100000
    return bint32_to_bytes(int32(v))
}

Thanks again for the great library.

[rowland]

In github.com/rowland/go-fb, I made timezone one of the database parameters. From the timezone, I got a time.Location using time.LoadLocation. The location became an attribute of the connection, allowing me to use time.ParseInLocation for user-supplied strings. For timestamps pulled from the database, I used the location to construct appropriate times.

func timeFromTimestamp(ts C.ISC_TIMESTAMP, loc *time.Location) (t time.Time) {
    unixDaySecs := (int64(ts.timestamp_date) * secsPerDay) - secsFromModifiedJulianDayToUnixEpoch
    unixTimeSecs := int64(ts.timestamp_time) / 10000
    unixFracSecs := int64(ts.timestamp_time) % 10000
    ns := unixFracSecs * 100000
    unixTime := unixDaySecs + unixTimeSecs
    t = time.Unix(unixTime, ns).In(time.UTC)
    if loc != time.UTC {
        y, m, d := t.Date()
        h, n, s := t.Clock()
        t = time.Date(y, m, d, h, n, s, t.Nanosecond(), loc)
    }
    return
}

[nakagami]

Firebird 4.0 or later has TimeZone support, so try Firebird 4.0.