Different timezone between parquet file and BQ table

[ffanalista]

Hello, 

I developed a Talend job that read from SQL Server, write a parquet file and load it on Google Storage. After that with LOAD DATA OVERWRITE command I load the data into a table in GBQ. 

But I have a problem with timestamp datatype: in SQL Server and Parquet file they have the same date and time instead the gbq table have a different date and hour: 

SQL Server\Parquet: 2022-07-10 00:00

GBQ Table: 2022-07-09 22:00:00 UTC

How can I fix this? 

Thank you 

F

[ErnestoC]

UTC is the default format used to store timestamps in BigQuery and a request to change this behavior has been previously raised in Google’s Issue Tracker. Even though there is no current ETA for this request, it’s currently in progress by the product teams. 

In the meantime, have you looked into the available TIMESTAMP and DATE functions available in BigQuery? 

[ffanalista]

ciao @ErnestoC 

The table is used by a scheduled query so we can add this Date function:

SELECT DATE(myDate, "Europe/Rome") as myDate_romeTZ
from myTable

But there are other tables loaded by Talend that are used for business BI and having the dates in a different time zone gives us inconsistent data.

We must modify these dashboard. 

Thank you very much 

Federico