Issues while converting timestamp to specific timezone and then converting it to date in bigquery
I am doing just a simple conversion of timestamp column value to specific timezone and then getting the date out of it to create analytical charts based on the output of the query.
I am having the column of type timestamp in the bigquery and value for that column is in UTC. Now I need to convert that to PST (which is -8:00 GMT) and was looking straight forward to convert but I am seeing some dates up and down based on the output I get.
From the output that I was getting I took one abnormal output and wrote a query out of it as below:
select "2021-05-27 18:10:10" as timestampvalue ,
Date(Timestamp("2021-05-27 18:10:10" ,"-8:00")) as completed_date1,
Date(Timestamp("2021-05-27 18:10:10","America/Los_Angeles")) as completed_date2,
Date(TIMESTAMP_SUB("2021-05-27 18:10:10", INTERVAL 8 hour)) as completed_date3,
Date(Timestamp("2021-05-27 18:10:10","America/Tijuana")) as completed_date4
The output that I get is as below:
Based on my understanding I need to subtract 8 hours from the time in order to get the timestamp value for the timezone that I wanted and according to that completed_date3 column seems to show the correct value that should be there but if I use other timezone conversions as suggested in google documentation, the output gets changed to 2021-05-28 and I am not able to understand how that can happen.
Can anyone let me know what is the thing that I am doing wrong?
I was actually using it in a wrong way. I need to use it as below :
select "2021-05-27 18:10:10" as timestampvalue ,
Date(Timestamp("2021-05-27 18:10:10") ,"-8:00") as completed_date1,
Date(Timestamp("2021-05-27 18:10:10"),"America/Los_Angeles") as completed_date2,
Date(TIMESTAMP_SUB("2021-05-27 18:10:10", INTERVAL 8 hour)) as completed_date3,
Date(Timestamp("2021-05-27 18:10:10"),"America/Tijuana") as completed_date4
Initially I was converting that string timestamp to a specific timestamp based on the timezone and that is what I did not want. Now if a convert a string to timestamp first without using time zone parameter and then apply timezone parameter when getting the date value out of it then it would return me correct date.
Please see the snapshot below :
