Saving time.Time in golang to postgres timestamp with time zone field

user_7648982 0
Viewed 2947

I'm trying to test my database (which is Postgres) models in golang. the problem is that a field value with time.Time type miss-match when I retrieved it from DB. here is the code:

a := myStruct{aTime: time.Now().UTC(), ...}
res, err := db.Exec("INSERT INTO my_table VALUES ($1, $2, ...) RETURNING id", a.aTime, ...)
...
// some logic for setting id in struct a
...
row := db.QueryRow("SELECT * FROM my_table WHERE id = $1", a.id)
var test *myStruct
row.Scan(test)
reflect.DeepEqual(a, test) // returns False
fmt.Printf("%v\n%v", a, test)
// {2020-02-25 12:37:16.906605805 +0000 UTC ...}
// {2020-02-25 12:37:16.906606 +0000 UTC ...}

and the time field type in the database is timestamp with time zone.

I tried the following too:

a.aTime.Equal(test.aTime) // returns false
a.aTime = a.aTime.Round(time.Second)
test.aTime = test.aTime.Round(time.Second)
a.aTime.Equal(test.aTime) // returns true

but still reflect.DeepEqual returns false. the problem is time field, because when i do the following the reflect.DeepEqual turns to true

a.aTime = test.aTime

Does anyone know how to fix this?

go postgresql
1 Answers

These times are not equal:

// {2020-02-25 12:37:16.906605805 +0000 UTC ...}
// {2020-02-25 12:37:16.906606 +0000 UTC ...}

the DB value has (rounded) micro-second precision - go's time has nano-second precision.

I would suggest rounding your times before you add them to the database to a level of precision that is supported by your DB and your needs e.g.

a.Atime = a.aTime.Round(time.Microsecond) // round to nearest micro (per Markus comment)

res, err := db.Exec("INSERT INTO my_table VALUES ($1, $2, ...) RETURNING id", a.aTime, ...)

Also to compare time equality, use time.Equal():

Equal reports whether t and u represent the same time instant. Two times can be equal even if they are in different locations. For example, 6:00 +0200 and 4:00 UTC are Equal. See the documentation on the Time type for the pitfalls of using == with Time values; most code should use Equal instead.

user_12185120
Related