Bigquery Time zone configuration

[sharontmathew]

Whenever using Current Date or Current Timestamp, Bigquery should automatically show values in EST. In addition, is it possible to remove the UTC suffix that gets displayed on BQ Console for each and every Timestamp Column.

[ms4446 · Accepted solution]

BigQuery's default timezone for CURRENT_DATE() and CURRENT_TIMESTAMP() is UTC. To automatically show values in Eastern Standard Time (EST), you need to convert the timezone within your queries.

Here's how to handle the conversion:

1. Convert UTC to EST for current date and timestamp:

SELECT TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(), "America/New_York")) AS current_timestamp_in_est,
       DATE(DATETIME(CURRENT_TIMESTAMP(), "America/New_York")) AS current_date_in_est;

This query converts the current timestamp to the "America/New_York" timezone (EST).

2. Remove UTC Suffix from Timestamps:

BigQuery displays timestamps with a 'UTC' suffix by default. To remove it:

SELECT FORMAT_TIMESTAMP("%F %T", CURRENT_TIMESTAMP(), "America/New_York") AS formatted_timestamp;

This formats the timestamp in 'YYYY-MM-DD HH:MM:SS' without the timezone suffix.

Remember:

• Apply these conversions in each query that needs EST or specific formatting.
• BigQuery doesn't have a global setting to enforce these changes by default.