Skip to content

Timestamp Conversion from Julian to Gregorian Causes Date Shift in Arrow Format (0001-01-01 00:00:00 => 0001-01-03 00:00:00) #1389

Closed

Description

@idleyui

Environment
Spark Version: 3.4
Spark BigQuery Connector Version: 0.41.0

Problem Description
When reading a timestamp value 0001-01-01 00:00:00 from BigQuery using the Arrow format in the Spark BigQuery Connector, the result is incorrectly shifted to 0001-01-03 00:00:00. This discrepancy does not occur when using the Avro format.

The issue arises from the use of rebaseJulianToGregorianMicros in the Arrow path (ArrowSchemaConverter.java#L550), which rebases the timestamp from the Julian calendar to the Gregorian calendar. This behavior is not present in the Avro path.

Reproduction Steps

  1. Create Test Data in BigQuery
CREATE OR REPLACE TABLE type_timestamp (
    ts TIMESTAMP
);

INSERT INTO type_timestamp VALUES ('0001-01-01 00:00:00');
  1. Spark Code (Arrow Format)
Dataset<Row> df = spark.read()
    .format("bigquery")
    .option("project", "project")
    .option("parentProject", "project")
    .option("table", "project.dataset.type_timestamp")
    .load();

df.show(200, false);
df.withColumn("sec", unix_timestamp(col("ts"))).show(200, false);
  1. Spark Code (Avro Format)
    Add the following configuration to force Avro format:
spark.conf().set("readDataFormat", "avro");

Results
Arrow Format (Incorrect)

+-------------------+------------+
|ts                 |sec         |
+-------------------+------------+
|0001-01-03 00:00:00|-62135424343|
+-------------------+------------+

Avro Format (Correct)

+-------------------+------------+
|ts                 |sec         |
+-------------------+------------+
|0001-01-01 00:00:00|-62135596800|
+-------------------+------------+

Root Cause Analysis
The discrepancy stems from how the Arrow and Avro formats handle timestamp conversions:

Arrow Path: Applies rebaseJulianToGregorianMicros to convert timestamps from the Julian calendar (used in older systems like BigQuery) to the Gregorian calendar.
Avro Path: Does not apply this rebasing, preserving the original timestamp.
This behavior is problematic for historical dates (e.g., 0001-01-01) because the Julian and Gregorian calendars differ by 2 days in this era. The Arrow path introduces an unintended shift, while the Avro path retains correctness.

Activity

  1. self-assigned this
    on Jul 18, 2025
  2. BoqianShi commented on Jul 21, 2025

    @BoqianShi
    Member

    Hi @idleyui,

    Thank you for the detailed bug report and your accurate analysis of the root cause. You are correct that the issue is due to an unconditional Julian-to-Gregorian calendar conversion in the Arrow reader path, which causes an incorrect offset for historical timestamps.

    Given that this affects a specific use case and a straightforward workaround is available, we have decided not to implement a fix in the Arrow reader at this time. For users working with historical dates (pre-1582), we recommend forcing the use of the Avro format by setting spark.conf.set("readDataFormat", "avro"). This will ensure the data is read correctly without unintended conversions.

    Thanks!

  3. idleyui commented on Feb 28, 2026

    @idleyui
    Author

    Hi @BoqianShi

    It appears that the Avro format has several compatibility issues:

    1. Failure to read the DATETIME type
    org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 0.0 failed 4 times, most recent failure: Lost task 0.3 in stage 0.0 (TID 3) (33.80.57.226 executor 1): java.lang.ClassCastException: org.apache.spark.unsafe.types.UTF8String cannot be cast to java.lang.Long
    
    1. Incorrect handling of NUMERIC precision

    bigquery sql

    create or replace table type_numeric
    (
        n2 numeric(29, 0),
        n3 numeric(38, 9)
    );
    
    insert into type_numeric
    values (NUMERIC '12345678901234567890123456789',
            NUMERIC '12345678901234567890123456789.123456789');
    
    
    insert into type_numeric
    values (NUMERIC '12345678901234567890123456789',
            NUMERIC '12345678901234567890123456789.123456');

    spark show result

    root
     |-- n2: decimal(29,0) (nullable = true)
     |-- n3: decimal(38,9) (nullable = true)
    
    +------------------------------+---------------------------------------+
    |n2                            |n3                                     |
    +------------------------------+---------------------------------------+
    |12345678901234567890.123456789|12345678901234567890123456789.123456789|
    |12345678901234567890.123456789|12345678901234567890123456789.123456789|
    |12345678901234567890.123456789|12345678901234567890123456789.123456000|
    +------------------------------+---------------------------------------+
    
    1. Errors with unparameterized NUMERIC types, I have set bigNumericDefaultPrecision=38 and bigNumericDefaultScale=18, the Arrow format respects these configurations perfectly and allows the data to be read successfully, but the avro path throws Exception:
    Caused by: org.apache.spark.SparkArithmeticException: [DECIMAL_PRECISION_EXCEEDS_MAX_PRECISION] Decimal precision 56 exceeds max precision 38.
    
  4. BoqianShi commented on Apr 30, 2026

    @BoqianShi
    Member

    Hi @idleyui,

    Thanks for pushing back on the Avro workaround — your feedback, plus the additional Avro-path bugs you flagged (DATETIME ClassCastException, NUMERIC precision handling, BigNumeric defaults), made us reconsider.

    I've landed PR #1473, which adds an enableArrowTimestampRebase read option. The default is true so existing behavior is preserved; setting it to false disables the Julian-to-Gregorian rebase in the Arrow reader path and resolves the 2-day shift on pre-Gregorian timestamps you reported.

    For example:

    spark.read
      .format("bigquery")
      .option("enableArrowTimestampRebase", "false")
      .load("project.dataset.type_timestamp")

    Thanks again for the detailed analysis on this one.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions

    ⌂ 换网址