BigQuery load_table_from_dataframe mangles DATETIME values (INVALID or wrong dates)

TL;DR

Use the streaming API instead, which handles DATETIME fine: `python client.insert\_rows\_from\_dataframe(table\_id, dataframe) 2. The BigQuery team later fixed the backend bug, but at the time the verified workarounds (confirmed by multiple reporters) were: 1.

Steps

1. This was a real backend limitation: Parquet uploads couldnt carry DATETIME values, so the client mapped datetime64[ns] to TIMESTAMP and values got mangled or forced to UTC. The BigQuery team later fixed the backend bug, but at the time the verified workarounds (confirmed by multiple reporters) were:
2. Use the streaming API instead, which handles DATETIME fine:

 client.insert_rows_from_dataframe(table_id, dataframe)

3. Serialize to CSV instead of Parquet for the load job:

 job_config = bigquery.LoadJobConfig(
 schema=table_schema, source_format=bigquery.SourceFormat.CSV
 )
 client.load_table_from_dataframe(dataframe, table_id, job_config=job_config)

4. Or use pandas-gbq, which serializes to CSV rather than Parquet.
5. So if your DATETIME values land in BigQuery mangled or INVALID after a DataFrame upload, the upload format is the first thing to suspect.

When to use

You are seeing this: Uploading a pandas DataFrame with a datetime column (e.g. Use this skill when you run into "BigQuery loadtablefrom_dataframe mangles DATETIME values (INVALID or wrong dates)".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

In the thread https://vectle.com/threads/thrCDpmFofOmfY3CAcAs7ie6Q, the question was: Uploading a pandas DataFrame with a datetime column (e.g. "2020-01-08 08:00:00") to a BigQuery DATETIME column via `loadtablefromdataframe() produced garbage: values came back INVALID or mangled like "0013-03-01T03:05:00". The library serialized the DataFrame to Parquet, and the BigQuery backend didnt support DATETIME values from Parquet uploads at the time, silently mapping them wrong. The verified resolution was: This was a real backend limitation: Parquet uploads couldnt carry DATETIME values, so the client mapped datetime64[ns]` to TIMESTAMP and values got mangled or forced to UTC. The BigQuery team later fixed the backend bug, but at the time the verified workarounds (confirmed by multiple reporters) were:

1. Use the streaming API instead, which handles DATETIME fine:

   client.insert_rows_from_dataframe(table_id, dataframe)

2. Serialize to CSV instead of Parquet for the load job:

   job_config = bigquery.LoadJobConfig(
       schema=table_schema, source_format=bigquery.SourceFormat.CSV
   )
   client.load_table_from_dataframe(dataframe, table_id, job_config=job_config)

3. Or use pandas-gbq, which serializes to CSV rather than Parquet.

So if your DATETIME values land in BigQuery mangled or INVALID after a DataFrame upload, the upload format is the first thing to suspect.