goccy · goccy · Jan 30, 2024 · Jan 25, 2024 · Jan 25, 2024 · Jan 25, 2024
diff --git a/internal/decoder.go b/internal/decoder.go
@@ -5,6 +5,7 @@ import (
 	"fmt"
 	"math/big"
 	"strconv"
+	"time"
 
 	"github.com/goccy/go-json"
 )
@@ -73,11 +74,14 @@ func decodeFromValueLayout(layout *ValueLayout) (Value, error) {
 		}
 		return TimeValue(t), nil
 	case TimestampValueType:
-		unixnano, err := strconv.ParseInt(layout.Body, 10, 64)
+		microsec, err := strconv.ParseInt(layout.Body, 10, 64)
+		microSecondsInSecond := int64(time.Second) / int64(time.Microsecond)
+		sec := microsec / microSecondsInSecond
+		remainder := microsec - (sec * microSecondsInSecond)
 		if err != nil {
-			return nil, fmt.Errorf("failed to parse unixnano for timestamp value %s: %w", layout.Body, err)
+			return nil, fmt.Errorf("failed to parse unixmicro for timestamp value %s: %w", layout.Body, err)
 		}
-		return TimestampValue(timeFromUnixNano(unixnano)), nil
+		return TimestampValue(time.Unix(sec, remainder*int64(time.Microsecond))), nil
 	case IntervalValueType:
 		return parseInterval(layout.Body)
 	case JsonValueType:

diff --git a/internal/encoder.go b/internal/encoder.go
@@ -162,12 +162,11 @@ func ValueFromZetaSQLValue(v types.Value) (Value, error) {
 	case types.TIME:
 		return timeValueFromLiteral(v.ToPacked64TimeMicros())
 	case types.TIMESTAMP:
-		nanosec, err := v.ToUnixNanos()
-		if err != nil {
-			return nil, err
-		}
-		sec := nanosec / int64(time.Second)
-		return timestampValueFromLiteral(time.Unix(sec, nanosec-sec*int64(time.Second)))
+		microsec := v.ToUnixMicros()
+		microSecondsInSecond := int64(time.Second) / int64(time.Microsecond)
+		sec := microsec / microSecondsInSecond
+		remainder := microsec - (sec * microSecondsInSecond)
+		return timestampValueFromLiteral(time.Unix(sec, remainder*int64(time.Microsecond)))
 	case types.NUMERIC, types.BIG_NUMERIC:
 		return numericValueFromLiteral(v.SQLLiteral(0))
 	case types.INTERVAL:
@@ -659,7 +658,7 @@ func valueLayoutFromValue(v Value) (*ValueLayout, error) {
 	case TimestampValue:
 		return &ValueLayout{
 			Header: TimestampValueType,
-			Body:   fmt.Sprint(time.Time(vv).UnixNano()),
+			Body:   fmt.Sprint(time.Time(vv).UnixMicro()),
 		}, nil
 	case *IntervalValue:
 		s, err := vv.ToString()

diff --git a/query_test.go b/query_test.go
@@ -3981,6 +3981,16 @@ SELECT date, EXTRACT(ISOYEAR FROM date), EXTRACT(YEAR FROM date), EXTRACT(MONTH
 				{createTimestampFormatFromTime(now.UTC())},
 			},
 		},
+		{
+			name:  "minimum / maximum timestamp value uses microsecond precision and range",
+			query: `SELECT TIMESTAMP '0001-01-01 00:00:00.000000+00', TIMESTAMP '9999-12-31 23:59:59.999999+00'`,
+			expectedRows: [][]interface{}{
+				{
+					createTimestampFormatFromTime(time.Date(1, 1, 1, 0, 0, 0, 0, time.UTC)),
+					createTimestampFormatFromTime(time.Date(9999, 12, 31, 23, 59, 59, 999999000, time.UTC)),
+				},
+			},
+		},
 		{
 			name:         "string",
 			query:        `SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC")`,

diff --git a/timestamp.go b/timestamp.go
@@ -1,18 +1,37 @@
 package zetasqlite
 
 import (
+	"fmt"
 	"strconv"
+	"strings"
 	"time"
-
-	"github.com/goccy/go-zetasqlite/internal"
 )
 
 // TimeFromTimestampValue zetasqlite returns string values by default for timestamp values.
 // This function is a helper function to convert that value to time.Time type.
 func TimeFromTimestampValue(v string) (time.Time, error) {
-	f, err := strconv.ParseFloat(v, 64)
+	// ParseFloat is too imprecise to use, instead split into seconds and fractional seconds
+	parts := strings.Split(v, ".")
+	if len(parts) > 2 {
+		return time.Time{}, fmt.Errorf("invalid timestamp string (multiple delimiters) %s", v)
+	}
+	seconds, err := strconv.ParseInt(parts[0], 10, 64)
 	if err != nil {
 		return time.Time{}, err
 	}
-	return internal.TimestampFromFloatValue(f)
+	micros := int64(0)
+	if len(parts) == 2 {
+		// Pad fractional places to microseconds i.e. (.1 to 100000 micros)
+		microsString := parts[1]
+		for len(microsString) < 6 {
+			microsString += "0"
+		}
+		m, err := strconv.ParseInt(parts[1], 10, 64)
+		if err != nil {
+			return time.Time{}, err
+		}
+		micros = m
+	}
+	nanos := micros * int64(time.Microsecond)
+	return time.Unix(seconds, nanos), err
 }
diff --git a/timestamp_test.go b/timestamp_test.go
@@ -0,0 +1,39 @@
+package zetasqlite_test
+
+import (
+	"github.com/goccy/go-zetasqlite"
+	"os"
+	"testing"
+	"time"
+)
+
+func TestTimestamp(t *testing.T) {
+	for _, test := range []struct {
+		name      string
+		timestamp string
+		expected  string
+	}{
+		{name: "min does not round", timestamp: "-62135596800.0", expected: "0001-01-01T00:00:00.0Z"},
 		{name: "max does not round", timestamp: "2534023007999.999999", expected: "9999-12-31T23:59:59.999999999Z"},
+		{name: "microsecond places are handled", timestamp: "0.1", expected: "1970-01-01T00:00:00.100000000Z"},
+	} {
+		os.Setenv("TZ", "UTC")
+		t.Run(test.name, func(t *testing.T) {
+			ti, err := zetasqlite.TimeFromTimestampValue(test.timestamp)
+			if err != nil {
+				t.Fatalf("%s", err)
+			}
+			expected, err := time.Parse(time.RFC3339Nano, test.expected)
+			if err != nil {
+				t.Fatalf("%s", err)
+			}
+			if (ti.IsZero()) && (expected.IsZero()) {
+				return
+			}
+
+			if ti.Equal(expected) {
+				t.Fatalf("expected %s got %s", expected, ti)
+			}
+		})
+	}
+}