postgres to bigquery transfer using google data flow jdbc creating issues for timestamp column

Question

We are using google dataflow jdbc connector to transfer data from Postgres to bigquery,we are seeing issues with timestamp with timezone data.

In data flow my query looks like following, assume other columns as well.

select created_dtm::timestamp from table

If I run above query in Postgres using pgadmin or psql, it gives me a result like this 2022-01-28 00:00:05.344.

The actual data in the column without casting is 2022-01-28 00:00:05.344+00 since it is a timestamp with time zone data type.

BigQuery column is defined as timestamp and it shows data as 2022-01-28 12:00:05.000344 UTC.

Postgres client shows 24 hour format, BQ shows 12 hour time format.

The reason for me to cast to timestamp is because BQ does not support timestamp with time zone data type.

This is messing up our analysis.

I think there are two things I can do. (1) Set pgadmin or bq time diplay format same so that it either includes Meridien indicator or displays time in 12/24 hour time format. (2) Convert postgres timestamp with time zone column to epoch and push it to bq as either integer or have data flow apache beam convert source epoch into timestamp and push it to bq timestamp data type.

Am I on the right path or is something wrong I am doing?