iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article
💽

Specifying Date Ranges in JST for BigQuery UTC Tables

に公開

Challenges

  • BigQuery stores data in UTC by default.
  • The challenge addressed in this article is specifying a range in JST for UTC data in BigQuery.

Conclusion

  • Create an intermediate table (CTE: Common Table Expressions) that generates strings in JST and converts them to UTC, then reference this intermediate table in the table you want to use.
  • When specifying using both date and time, change the first argument of the FORMAT_TIMESTAMP function to %F %T.
WITH
--
-- Prepare UTC and JST TIMESTAMPs for the start time
--
start_date_jst AS (
  SELECT
    FORMAT_TIMESTAMP('%F 00:00:00', TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), 'Asia/Tokyo') AS start_date_jst
),
start_date_utc AS (
  SELECT
    PARSE_TIMESTAMP('%F %T', start_date_jst.start_date_jst, 'Asia/Tokyo') AS start_date_utc
  FROM
    start_date_jst
),
--
-- Prepare UTC and JST TIMESTAMPs for the end time
--
end_date_jst AS (
  SELECT
    FORMAT_TIMESTAMP('%F 00:00:00', CURRENT_TIMESTAMP(), 'Asia/Tokyo') AS end_date_jst
),
end_date_utc AS (
  SELECT
    PARSE_TIMESTAMP('%F %T', end_date_jst.end_date_jst, 'Asia/Tokyo') AS end_date_utc
  FROM
    end_date_jst
)
SELECT start_date_jst, start_date_utc, end_date_jst, end_date_utc
FROM start_date_jst, start_date_utc, end_date_jst, end_date_utc;

Execution Results

start_date_jst start_date_utc end_date_jst end_date_utc
2024-06-23 00:00:00 2024-06-22 15:00:00 UTC 2024-06-24 00:00:00 2024-06-23 15:00:00 UTC

Explanation of Each Function

  1. FORMAT_TIMESTAMP
  1. TIMESTAMP_SUB
  1. CURRENT_TIMESTAMP
  1. PARSE_TIMESTAMP

SQL Structure

  1. start_date_jst
    Generates a timestamp string for midnight (JST) of the day before the current date.

  2. start_date_utc
    Converts start_date_jst into a UTC timestamp.

  3. end_date_jst
    Generates a timestamp string for midnight (JST) of the current date.

  4. end_date_utc
    Converts end_date_jst into a UTC timestamp.

  5. SELECT clause
    Joins these CTEs and outputs the JST and UTC timestamps for both the start and end dates.

Discussion