feat(bigquery-jdbc): add picosecond precision support for TIMESTAMP - #14031

b/544843125
b/544839231

This PR introduces support for picosecond precision (up to 12 fractional digits) when reading BigQuery TIMESTAMP columns and standardizes timestamp string representation across both Arrow and JSON engines.

Key Architectural Notes

1. Picosecond Precision Gating (EnableTimestampPicos):

   • Picosecond precision (up to 12 fractional digits) and expanded metadata are disabled by default and activated via the new EnableTimestampPicos connection property.
   • When disabled (default): Timestamps maintain standard 6-digit microsecond precision across metadata (displaySize=26, precision=26, scale=6), getString(), getTimestamp(), and getObject().
   • When enabled on picosecond columns: Reports displaySize=32, precision=32, scale=12 in ResultSetMetaData and preserves full 12-digit picosecond precision in getString().

2. TIMESTAMP getString() Formatting Normalization:

   • Previously, getString() on the JSON REST path returned raw numeric epoch strings (e.g., "1408452095.22" or "1.6905474E9"), diverging from the Arrow engine.
   • getString() on the JSON path now standardizes all TIMESTAMP columns to formatted UTC timestamp strings ("yyyy-MM-dd HH:mm:ss.ffffff"), ensuring consistent, JVM-timezone-independent representation across both Arrow and JSON engines regardless of the flag. EnableTimestampPicos controls the fractional digit scale (6 vs. up to 12 digits).

Changes Made

• Configuration:

  • Added the EnableTimestampPicos connection property in BigQueryConnection and DataSource.

• Arrow Storage Engine:

  • Configured ReadSession creation in BigQueryStatement to explicitly request TIMESTAMP_PRECISION_PICOS when EnableTimestampPicos is enabled.
  • Added type coercions for Text -> Timestamp and String -> Timestamp in BigQueryTypeCoercionUtility so getObject() and getTimestamp() seamlessly handle Arrow picosecond VarCharVector columns.

• JSON REST Engine:

  • Updated BigQueryJsonResultSet.getString() to format timestamp fields using UTC anchoring.
  • Replaced custom string slicing with a unified mathematical parser in BigQueryTemporalUtility.parseEpochDecimalToInstant() using BigDecimal flooring (RoundingMode.DOWN). This losslessly handles standard decimals, scientific notation (e.g., "1.6905474E9"), whole integer epochs, and pre-1970 negative epoch decimals.
  • Updated FieldValueToTimestamp to delegate directly to parseEpochDecimalToInstant(), preserving nanosecond precision for getTimestamp() and getObject().

• JDBC Metadata:

  • Updated BigQueryResultSetMetadata to report displaySize=32, precision=32, and scale=12 when EnableTimestampPicos is active and the column schema precision is 12.

• Temporal Utilities & Precision Handling:

  • Deterministic Truncation: Sub-second precision reduction uses deterministic truncation (RoundingMode.DOWN) rather than rounding to avoid timestamp boundary rollovers (e.g., rounding 23:59:59.9999999 to the next day).
  • boxTimestamp(): Gracefully truncates 12-digit picosecond strings to 9 digits (nanoseconds) for java.sql.Timestamp, which maxes out at nanosecond precision and would otherwise throw a DateTimeParseException.
  • Formatting Utilities: Added formatTimestampStringFromMicroseconds(), formatTimestampStringFromIso(), and formatTimestampString().

Testing

• Unit Tests:
  • Added tests in BigQueryTemporalUtilityTest and FieldValueTypeBigQueryCoercionUtilityTest verifying scientific notation, positive/negative pre-1970 epoch timestamps, and deterministic truncation for >6 and >9 digit fractions.
  • Added mocked Arrow vector tests in BigQueryArrowResultSetTest verifying getString(), getTimestamp(), and getObject() against picosecond Text columns.
  • Added metadata tests in BigQueryResultSetMetadataTest verifying precision, scale, and display size with picoseconds enabled and disabled.
  • Verified REPEATED and RECORD timestamp handling in BigQueryJsonResultSetTest.