Understanding TimeZone in BigQuery

Question

We have a requirement to store the timestamp column in PST and the same column is being used to partition the table. I see that though I have converted the column value as PST, BigQuery is still showing UTC timezone.

Is there any way we can store data in a timezone other than UTC? Here is the method which I have used to create this column.

public static Timestamp getTimestampInPST() {
    ZonedDateTime nowPST = ZonedDateTime.ofInstant(Instant.now(), TimeZone.getTimeZone("PST").toZoneId());
    return Timestamp.valueOf(nowPST.toLocalDateTime());
}

Answer 1

To solve this problem you can use 'Datetime' data type. Output difference is given below for the query:-

Query:

select CURRENT_TIMESTAMP() as TS, CURRENT_DATETIME() as DT;

Output:-

Second part of the solution:-

If your datetime (data) are in UTC then you can change your data (datetime to PST) through following SQL:-

SELECT CURRENT_DATETIME(), DATETIME_SUB( CURRENT_DATETIME(), INTERVAL 8 hour);

Else if your data (datetime) are in PST then try to insert data without substracting 8 hours from your data. If your column data type is 'Datetime', then you will not have same problem.