bigquery: civil.DateTime and civil.Time fail to insert

[lgrote]

Client

BigQuery

Environment

distroless docker on GKE

Go Environment

$ go version go1.22.4 darwin/arm64
$ ❯ go env
GO111MODULE=''
GOARCH='arm64'
GOBIN=''
GOCACHE='/Users/lgr/Library/Caches/go-build'
GOENV='/Users/lgr/Library/Application Support/go/env'
GOEXE=''
GOEXPERIMENT=''
GOFLAGS=''
GOHOSTARCH='arm64'
GOHOSTOS='darwin'
GOINSECURE=''
GOMODCACHE='/Users/lgr/workspace/go/pkg/mod'
GONOPROXY=''
GONOSUMDB=''
GOOS='darwin'
GOPATH='/Users/lgr/workspace/go'
GOPRIVATE=''
GOPROXY='https://proxy.golang.org,direct'
GOROOT='/usr/local/go/'
GOSUMDB='sum.golang.org'
GOTMPDIR=''
GOTOOLCHAIN='auto'
GOTOOLDIR='/usr/local/go/pkg/tool/darwin_arm64'
GOVCS=''
GOVERSION='go1.22.4'
GCCGO='gccgo'
AR='ar'
CC='clang'
CXX='clang++'
CGO_ENABLED='1'
GOMOD='/Users/lgr/workspace/go/xxxx'
GOWORK=''
CGO_CFLAGS='-O2 -g'
CGO_CPPFLAGS=''
CGO_CXXFLAGS='-O2 -g'
CGO_FFLAGS='-O2 -g'
CGO_LDFLAGS='-O2 -g'
PKG_CONFIG='pkg-config'
GOGCCFLAGS='-fPIC -arch arm64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -ffile-prefix-map=/var/folders/ry/1805fhs14_n982jppzrh0v0h0000gn/T/go-build1957741136=/tmp/go-build -gno-record-gcc-switches -fno-common'

Code

import (
	"strings"
	"testing"

	"cloud.google.com/go/bigquery"
	"cloud.google.com/go/civil"
)

func TestCivilDateTimeString(t *testing.T) {

	dt1, _ := civil.ParseDateTime("2006-01-02T15:04:05.99999")
	dt2, _ := civil.ParseDateTime("2006-01-02T15:04:05.999999")
	dt3, _ := civil.ParseDateTime("2006-01-02T15:04:05.9999999")
	tests := []struct {
		d civil.DateTime
	}{
		{dt1},
		{dt2},
		{dt3},
	}

	for _, tt := range tests {
		formatted := bigquery.CivilDateTimeString(tt.d)
		i := strings.LastIndex(formatted, ".")
		microSecondLength := len(formatted[i+1:])

		t.Logf("Raw: %v\tFormatted: %s \tMicroSecondLength: %d", tt.d, formatted, microSecondLength)
		if microSecondLength > 6 {
			t.Errorf("Microsecond length is %d, expected <= 6", microSecondLength)
		}
	}
}

Expected behavior

A civil.DateTime or civil.Time formatted with the functions bigquery.CivilDateTimeString or bigquery.CivilTimeString should be insertable into bigquery with the DATETIME and TIME datatypes.

Actual behavior

If the Nanoseconds of a civil.Time are rounded up (like dt3 above) bigquery does not accept the value and reponds with an error like this:

row insertions failed (insertion of row [insertID: "EzTAFi6QkJAggrZdB0rKdv18oxQ"; insertIndex: 927] failed with error: {Location: "timestamp"; Message: "Invalid datetime string \"2024-07-02 05:12:21.1000000\""; Reason: "invalid"}

The reason seems to be that the string format accepted by DATETIME and TIME expects a maximum of 6 fractional digits cd. Data type docs

However, bigquery.CivilTimeString can produce more fractional digits, as can be seen in the test above.

Context

For me the error occured while using the Uploader and saving structs that use civil.DateTime as timestamp. I tracked the error down to the bigquery.CivilTimeString function.

[lgrote]

No sure if this is an elegant way to solve this. However, this works for me.

func CivilTimeString(t civil.Time) string {
	if t.Nanosecond == 0 {
		return t.String()
	}
	micro := (t.Nanosecond + 500) / 1000 // round to nearest microsecond
	t.Nanosecond = 0
	if micro == 1000000 { // round to nearest second
		t.Second = t.Second + 1
		return t.String()
	}
	return t.String() + fmt.Sprintf(".%06d", micro)
}

func TestCivilTimeString(t *testing.T) {

	tests := []struct {
		s    string
		want string
	}{
		{"15:04:05.99999", "15:04:05.999990"},
		{"15:04:05.999999", "15:04:05.999999"},
		{"15:04:05.9999999", "15:04:06"},
		{"15:04:05.9999995", "15:04:06"},
		{"15:04:05.9999994", "15:04:05.999999"},
		{"15:04:05.5000009999", "15:04:05.500001"},
	}

	for _, tt := range tests {
		ti, err := civil.ParseTime(tt.s)
		if err != nil {
			t.Fatalf("Error parsing time: %v", err)
		}
		formatted := CivilTimeString(ti)
		if formatted != tt.want {
			t.Errorf("want %s, got %s", tt.want, formatted)
		}
	}
}