GBQ issues

[Alexian]

Wanted to do suppression of time in case of changing from/to daylight saving time.

I know for teradata there is function timezone_hour which will do the work easily.

However, I want to achieve same in BQ.

My requirements written below:

(Extract (timezone_hour from first_day)

Here in above statement I am assuming daylight saving has not started)

minus

Extract(timezone_hour from first_day +1 )In above statement daylight saving started. So at the same time there will forward/backward )

I need that exact number of hours between two days when the daylight started. 

[ms4446]

To effectively manage daylight saving time (DST) changes in BigQuery, follow these guidelines:

1. Store Timestamps in UTC:

   • Consistently store all timestamps in UTC using the TIMESTAMP data type. This approach ensures uniformity in time representation, irrespective of DST changes.

2. Apply Timezone Conversions During Data Retrieval:

   • Convert UTC timestamps to local timezones as needed during data retrieval. Utilize DATETIME(timestamp_column, timezone_region) for this conversion. This function automatically adjusts for DST in the specified timezone.

3. Accurately Calculate Time Differences:

   • Use TIMESTAMP_DIFF to compute the difference between timestamps. When both timestamps are converted to the same timezone, this function accurately accounts for any DST adjustments.

4. Carefully Handle DST Transitions:

   • Pay special attention to days where DST starts or ends, as these days might have 23 or 25 hours. While EXTRACT(HOUR FROM timestamp_column) can be used to extract the hour component of a timestamp, it does not directly indicate a DST shift.

5. Example Query:

   • Here's an example SQL query to calculate the hour difference, considering DST changes:   

     SELECT TIMESTAMP_DIFF(
       DATETIME(timestamp_column, "America/Los_Angeles"),
       DATETIME(timestamp_column - INTERVAL 1 DAY, "America/Los_Angeles"),
       HOUR
     ) AS hours_difference
     FROM your_table
     

   This query assumes timestamp_column is stored in UTC and converts it to a specific timezone, accounting for DST changes.

6. Key Considerations:

   • Always store timestamps in UTC to avoid inconsistencies due to local time changes.
   • Convert timestamps to local timezones during data retrieval, considering DST.
   • Use TIMESTAMP_DIFF for precise time difference calculations, especially around DST transitions.
   • Be cautious and aware of the peculiarities of DST transition days.