Big query not converting the time but just changing the time zone-CodePudding

Step # 1 -- In my big query table, I created (in SQL) and saved a field (create_time) in Pacific time (America/Los_Angeles) and it appears like this -> 2022-05-31T06:07:46.044000 .

update: This create time field is created from a UTC time field in big query table. I am just doing the following to create the create_time field -> datetime(existing_create_time, America/Los_Angeles ), where the existing one is in UTC.

Step #2 -- Now in a python script, I am reading step#1 table (using client.query() method of big query), the create_time along with few other variables, performing some operations on other fields (create_time is untouched) and saving these back to a different big query table (using the 'load_table_from_dataframe' method in BQ to save pandas dataframe to big query table).

update: After reading into the pandas dataframe, the value is a timestamp like Timestamp('2022-05-31 06:07:46.044000')

Output from Step #2 -- Now, in the saved BQ table from the step#2, I see the create_time field appears like this --> 2022-05-31 06:07:46.044000 UTC

update: so the big query table is created by the pandas dataframe, and the data type associated withthe BQ output date field is timestamp. I have no control over this data type as it is automatically created by load_table_From_Dataframe() method

The problem with the step#2 output is, Big query is just changing the time zone of the create_time to UTC without converting the time to UTC. For example, if I initially have 10:00 PST, when actually converting to UTC, it should be 17:00 UTC. But when saving the output in step#2, big query is saving the time as 10:00 UTC (changing the time zone without converting the time to the corresponding time zone).

Is there a way to fix this -> have BQ convert the time as well, along with time zone change (or) other alternative is to explicitly state in python that the incoming create_Time (while reading it from big query) is in pacific time stamp ?

CodePudding user response：

I Solved the issue by explicitly stating the time zone when reading the BQ data in my python job in step #2 . Used -> timestamp(create_time, 'America/Los_Angeles')