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_TIMESTAMPfunction 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
FORMAT_TIMESTAMP
- Converts a timestamp to a string according to the specified format. Here, a UTC date is converted to JST in the format "YYYY-MM-DD 00:00:00".
- Official documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#format_timestamp
TIMESTAMP_SUB
- Subtracts a specific time interval from a specified timestamp. Here, one day is subtracted from the current timestamp.
- Official documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_sub
CURRENT_TIMESTAMP
- Returns the current UTC timestamp.
- Official documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp
PARSE_TIMESTAMP
- Parses a string into a timestamp. Here, a JST format date string is converted to a UTC timestamp.
- Official documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#parse_timestamp
SQL Structure
-
start_date_jst
Generates a timestamp string for midnight (JST) of the day before the current date. -
start_date_utc
Convertsstart_date_jstinto a UTC timestamp. -
end_date_jst
Generates a timestamp string for midnight (JST) of the current date. -
end_date_utc
Convertsend_date_jstinto a UTC timestamp. -
SELECT clause
Joins these CTEs and outputs the JST and UTC timestamps for both the start and end dates.
Discussion