Update in Project default_time_zone not affecting timestamp-CodePudding

As part of setting GCP project level timezone to AEST, I have run the following command -

ALTER PROJECT `gcp-abc-def`
SET OPTIONS ( `region-us.default_time_zone` = 'Australia/Sydney')

Doing so, I see that current_datetime() is getting changed to AEST whereas timestamp remains UTC, as can be seen below.

Can someone help how this can be remedied? What other settings to be changed?

CodePudding user response：

I see that current_datetime() is getting changed to AEST whereas timestamp remains UTC this is because the current_timestamp function shows time in timestamp type. A timestamp does not have a time zone; it represents the same instant in time globally. When querying for CURRENT_TIMESTAMP() it shows explicitly in UTC by having zero time zone offset. When you convert a timestamp to some other type that isn't tied to a particular timezone, you can specify the timezone for the conversion.You can use format_timestamp to convert the timestamp into your zone specific time.

Example:

ALTER PROJECT `gcp-abc-def`
SET OPTIONS ( `region-us.default_time_zone` = 'Australia/Sydney');

select current_datetime() as cdt, current_timestamp() as cts,format_timestamp('%c',current_timestamp(),'Australia/Sydney') as cts2