BigQuery Table - timestamp column with timezone

[mmadasamy]

Team,

Can we create timestamp columns with timezone in to bq table?

My requirement is i want to store three timestamp columns with different timezone on single bq table?Currently all columns it's pointing to UTC time, Without using timestamp_convert method can i get the the timestamp values with respective timezone?

[ms4446]

In Google Cloud BigQuery, you cannot directly store timestamp columns with timezone information in the table's schema. BigQuery internally stores timestamps in UTC, regardless of the timezone specified during ingestion. However, you can achieve your requirement of storing and retrieving timestamp values with respective timezones by utilizing the DATETIME and TIMESTAMP functions.

Understanding DATETIME and TIMESTAMP:

• DATETIME: DATETIME represents a specific calendar date and time combination, but it doesn't inherently include timezone information. It's useful for storing local dates and times without the need for explicit timezone conversion.

• TIMESTAMP: TIMESTAMP, on the other hand, represents an absolute point in time, specifically in UTC (Coordinated Universal Time). It's a universal representation of time, independent of any particular timezone.

Storing and Retrieving Timestamps with Timezones:

• Storage: When storing timestamps in BigQuery, it's best practice to store them in UTC format. This ensures consistency and avoids issues with daylight saving time changes across different timezones.

• Retrieval: For retrieving timestamps with specific timezone information, utilize the TIMESTAMP(DATETIME(event_time, "America/Los_Angeles")) function. This function effectively converts the UTC timestamp to the specified timezone.

• Timezone Conversion: Timezone conversion should ideally be handled at the application level or during data retrieval rather than during data ingestion. This allows for flexibility and control over how timestamps are presented to users.

Best Practices and Documentation:

• Best Practices: Follow established best practices for handling timestamps in databases, such as storing them in UTC and converting to local timezones when necessary.

• Documentation Reference: Refer to the official BigQuery documentation for the latest functions, syntax, and best practices. Google regularly updates its services and functionalities, and the documentation remains the most authoritative source of information.

By adhering to these guidelines and utilizing the appropriate functions, you can effectively work with timestamps and timezones in BigQuery, ensuring data consistency and accuracy for your applications.

[mmadasamy]

Hi @ms4446,

Thanks for quick response. Though i used suggested function on my query, time is correctly converting to PST but the timezone still it shows as UTC for below query.

Screenshot:

Query:

SELECT

CURRENT_TIMESTAMP() AS current_timestamp_utc,

TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(), "America/Los_Angeles"))as current_timestamp_pst

[ms4446 · Accepted solution]

The behavior you're observing is due to the way BigQuery handles timestamps. In BigQuery, a TIMESTAMP data type represents an absolute point in time, and it is always displayed in UTC when queried, regardless of any timezone conversions you apply.

When you use the TIMESTAMP(DATETIME(timestamp, timezone)) function, you are effectively converting the timestamp to a different timezone, but the result is still a TIMESTAMP value, which BigQuery displays in UTC.

Here's a breakdown of your query:

• CURRENT_TIMESTAMP() returns the current timestamp in UTC.
• TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(), "America/Los_Angeles")) converts the current UTC timestamp to the "America/Los_Angeles" timezone, but the result is still a TIMESTAMP and is displayed in UTC.

To see the time in the Pacific Standard Time (PST) timezone, you would need to use a STRING representation of the datetime, or use a DATETIME type which retains the timezone conversion but does not store timezone information. For example:

SELECT CURRENT_TIMESTAMP() AS current_timestamp_utc, DATETIME(CURRENT_TIMESTAMP(), "America/Los_Angeles") as current_timestamp_pst

This query will show current_timestamp_pst in PST, but as a DATETIME value, not as a TIMESTAMP. Remember, DATETIME does not store timezone information, but it will display the converted time correctly.