在Go中的mongodb驱动程序中进行比较以及可能以其他语言和其他数据库进行比较时的时间精度问题

Question

I was learning Go and Mongodb, currently using the alpha official mongodb driver. Although it is in alpha, it is quite functional for basic usage I think. But I got an interesting issue on time conversion in this db driver.

Basically, I created a custom typed struct object, and marshaled it to bson document, and then convert the bson document back to struct object.

//check github.com/mongodb/mongo-go-driver/blob/master/bson/marshal_test.go
func TestUserStructToBsonAndBackwards(t *testing.T) {
u := user{
    Username:          "test_bson_username",
    Password:          "1234",
    UserAccessibility: "normal",
    RegisterationTime: time.Now(), //.Format(time.RFC3339), adding format result a string
}

//Struct To Bson
bsonByteArray, err := bson.Marshal(u)
if err != nil {
    t.Error(err)
}
//.UnmarshalDocument is the same as ReadDocument
bDoc, err := bson.UnmarshalDocument(bsonByteArray)
if err != nil {
    t.Error(err)
}
unameFromBson, err := bDoc.LookupErr("username")
//so here the binding is working for bson object too, the bind field named username ratherthan Username
if err != nil {
    t.Error(err)
}
if unameFromBson.StringValue() != "test_bson_username" {
    t.Error("bson from user struct Error")
}

//Bson Doc to User struct
bsonByteArrayFromDoc, err := bDoc.MarshalBSON()
if err != nil {
    t.Error(err)
}

var newU user
err = bson.Unmarshal(bsonByteArrayFromDoc, &newU)
if err != nil {
    t.Error(err)
}

if newU.Username != u.Username {
    t.Error("bson Doc to user struct Error")
}

//here we have an issue about time format.
if newU != u {
    log.Println(newU)
    log.Println(u)
    t.Error("bson Doc to user struct time Error")
}
}

However since my struct object has a time field, the result struct object contains a less accurate time value than the original. Then the comparison is failed.

=== RUN   TestUserStructToBsonAndBackwards
{test_bson_username 1234     0001-01-01 00:00:00 +0000 UTC   2018-08-28 23:56:50.006 +0800 CST 0001-01-01 00:00:00 +0000 UTC normal }
{test_bson_username 1234     0001-01-01 00:00:00 +0000 UTC   2018-08-28 23:56:50.006395949 +0800 CST m=+0.111119920 0001-01-01 00:00:00 +0000 UTC normal }
--- FAIL: TestUserStructToBsonAndBackwards (0.00s)
    model.user_test.go:67: bson Doc to user struct time Error

So I would like to ask many questions from this.

1. How to compare time properly in this case ?

2. What's the best way to store time in database to avoid such precision issue ? I think the time in database should not be a string.

3. is this a db driver bug ?

Answer 1

Times in BSON are represented as UTC milliseconds since the Unix epoch (spec). Time values in Go have nanosecond precision.

To round trip time.Time values through BSON marshalling, use times truncated to milliseconds since the Unix epoch:

func truncate(t time.Time) time.Time {
    return time.Unix(0, t.UnixNano()/1e6*1e6)
}

...

u := user{
    Username:          "test_bson_username",
    Password:          "1234",
    UserAccessibility: "normal",
    RegisterationTime: truncate(time.Now()), 
}

You can also use the Time.Truncate method:

u := user{
    Username:          "test_bson_username",
    Password:          "1234",
    UserAccessibility: "normal",
    RegisterationTime:  time.Now().Truncate(time.Milliseconds),
}

This approach relies on the fact that Unix epoch and Go zero time differ by a whole number of milliseconds.