Bigquery Time zone configuration

Google Cloud Data Analytics
2

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.
2 Likes