Truncate time.Time to microsecond resolution before inserting

[joeshaw]

Postgres (at least 9.3) can only store timestamps in microsecond resolution. Go supports nanosecond resolution and pq formats them in RFC3339Nano format, which includes nanoseconds.

Postgres takes the nanoseconds and rounds them to the nearest microsecond. Go's time.Time has a Truncate method which will truncate, rather than round, to the provided precision.

When writing unit tests to ensure that the data written to the database is as expected, I (half of the time) get failures due to the different behavior. It would seem like truncating the time.Time to microsecond resolution before inserting in Postgres is the right thing to do, to get more predictable behavior between Postgres and Go.

[msakrejda]

Good catch. Technically, this is silent data loss akin to the extra_float_digits issues, but I think putting the burden on the user to truncate here is preposterous. But why not Time.Round rather than Time.Trunate?

[joeshaw]

Here's a test program that demonsrtates:

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/lib/pq"
)

func panicIfErr(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {
    db, err := sql.Open("postgres", "sslmode=disable")
    panicIfErr(err)
    defer db.Close()

    _, err = db.Exec(`CREATE TEMP TABLE joeshaw_time_test ("t" timestamp with time zone);`)
    panicIfErr(err)

    goTime := time.Date(2014, 1, 31, 14, 50, 20, 720408938, time.Local)
    fmt.Println("Original Go time:", goTime)
    _, err = db.Exec(`INSERT INTO joeshaw_time_test VALUES ($1);`, goTime)
    panicIfErr(err)

    rows, err := db.Query(`SELECT t FROM joeshaw_time_test;`)
    panicIfErr(err)
    defer rows.Close()

    var sqlTime time.Time
    for rows.Next() {
        err := rows.Scan(&sqlTime)
        panicIfErr(err)
        fmt.Println("Time retrieved from pg:", sqlTime)
        break
    }

    err = rows.Err()
    panicIfErr(err)

    truncGoTime := goTime.Truncate(time.Microsecond)
    fmt.Println("Go time truncated to ms:", truncGoTime)

    fmt.Println("sqlTime == truncGoTime:", truncGoTime.Equal(sqlTime))
}

[joeshaw]

@deafbybeheading oh, because my eyes apparently aren't working and I didn't see time.Round in the docs at all. Just completely missed it.

Welp, that'll work fine for me then. Shall I close this?

[msakrejda]

@joeshaw nah, I'd like to leave it open--I think we should explicitly round in pq rather than depend on the Postgres rounding behavior. Glad you're unblocked, though.

[johto]

I agree, we should explicitly truncate or round and document the behaviour. Naively thinking I think we ought to truncate (in order not to push timestamps into the future), but I doubt any of that matters in the case of nanoseconds vs. microseconds. Rounding would preserve backwards compatibility, if that's what postgres is doing. The user can truncate before handing the time.Time to database/sql if that's the behaviour he wants. So I guess my vote is for rounding.

[johto]

Actually, I traced down the code (DecodeNumberField in src/backend/utils/adt/datetime.c) and it calls rint(), which rounds halfway values to the nearest even value:

local:marko=#* select '2013-01-01 00:00:00.0123355'::timestamptz;
          timestamptz          
-------------------------------
 2013-01-01 00:00:00.012336+01
(1 row)

local:marko=#* select '2013-01-01 00:00:00.0123365'::timestamptz;
          timestamptz          
-------------------------------
 2013-01-01 00:00:00.012336+01
(1 row)

local:marko=#* select '2013-01-01 00:00:00.0123367'::timestamptz;
          timestamptz          
-------------------------------
 2013-01-01 00:00:00.012337+01
(1 row)

time.Round() guarantees to round them up. So both time.Round() and time.Truncate() will break backwards compatibility.

[johto]

Any thoughts on this? I think we should just Round() in pq. This would still break roughly 1/20 of the time, but I'd be surprised if someone was relying on PG's behaviour in their code or test cases right now.

[msakrejda]

Yeah, I think Round() is probably reasonable.

[deathwish]

When I ran into this (also in unit tests) I used Round() to solve it, so it's precisely the expected behavior in my opinion.

[johto]

Of course, time.Round() and time.Truncate() are Go 1.1+ only :-(.

[johto]

Now that we're not stuck on Go 1.0 anymore, I think we should time.Truncate() here. It's less compatible with the old behaviour than Round(), but it feels icky pushing times into the future, even if it's about microseconds. What do you think?

[cbandy]

I think we should leave this to the user. It is true that Postgres and Go have differing precisions for temporal values. Users (and their tests) that are concerned about nanosecond precision should notice (and their tests should fail.)

I think it is the responsibility of the driver to ensure values it sends to the backend are semantically equivalent to the values it receives from the user. Conversely, it should ensure values received from the backend are semantically equivalent to the values it sends to the user.

[PSUdaemon]

I agree with @cbandy here. Don't make assumptions on what the user might want. I think the real fix is a patch to postgres to support nanosecond precision.

[johto]

I think the real fix is a patch to postgres to support nanosecond precision.

That's never gonna happen, so it's either time.Truncate() or status quo.

[ST-DDT]

I think the real fix is a patch to postgres to support nanosecond precision.

That's never gonna happen

@johto Is there a special reason for that? I cannot find any discussion regarding that.

[johto]

Is there a special reason for that?

You'd have to either make the on-disk footprint of timestamps bigger than it already is (8 bytes), or greatly reduce the range of possible timestamps for (at least with current hardware) no gain. And in both cases breaking backwards compatibility for an absolutely massive number of applications.

[leighmcculloch]

@johto Does rint() always round to nearest? I thought the method that rint uses for rounding is configurable via fromfp.

[anicoll]

PR to resolve this issue with Postgres accuracy limit
#954

https://www.postgresql.org/docs/current/datatype-datetime.html
This means that we don't lose any accuracy until we insert into Postgres with as much accuracy as possible

[chabad360]

Is there a special reason for that?

You'd have to either make the on-disk footprint of timestamps bigger than it already is (8 bytes), or greatly reduce the range of possible timestamps for (at least with current hardware) no gain. And in both cases breaking backwards compatibility for an absolutely massive number of applications.

@johto Why couldn't Postgres have a TimestampN (or something like that) which would support nanosecond precision?
It wouldn't break anything, and it available for anyone who needs it (ex. us).