GBQ issues

Google Cloud Database
,
1

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.

2

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.