Be Careful with @run_date in BigQuery Scheduled Queries – Especially in KST/JST!

In Google BigQuery, there’s a convenient function called @run_date. It automatically sets the reference date for your query to the time it is executed. Sounds great, right? Well... only if you're manually running the query from the Scheduled Queries UI. If you set it to run automatically at a scheduled time, and you haven’t explicitly set the timezone, the query will run in UTC. Here’s the catch: For countries like South Korea or Japan(UTC+9), this can lead to unexpected behavior, especially if your query runs across midnight UTC. A job scheduled for 9:00 AM KST will actually run at midnight UTC, and @run_date will evaluate to the previous date in local time. That’s a nasty, silent bug waiting to happen. To avoid this mess, always set the timezone explicitly when scheduling queries. And more importantly, use @run_time instead of @run_date if you need to control the exact time, with timezone. -- You don't need to wrap with DATE() unless you need a DATE type DATE(@run_time, 'Asia/Seoul') DATE(@run_time, 'Asia/Tokyo') Unfortunately, many of the datamarts in our team were built using @run_date without timezone awareness, so now there’s a mountain of fixes ahead. But hey... we'll survive. Somehow. Ha! Reference: https://qiita.com/sushi_edo/items/8250902ce2af778c2e8f

Jun 19, 2025 - 16:40

Be Careful with @run_date in BigQuery Scheduled Queries – Especially in KST/JST!

In Google BigQuery, there’s a convenient function called @run_date. It automatically sets the reference date for your query to the time it is executed. Sounds great, right?

Well... only if you're manually running the query from the Scheduled Queries UI. If you set it to run automatically at a scheduled time, and you haven’t explicitly set the timezone, the query will run in UTC.

Here’s the catch:
For countries like South Korea or Japan(UTC+9), this can lead to unexpected behavior, especially if your query runs across midnight UTC.
A job scheduled for 9:00 AM KST will actually run at midnight UTC, and @run_date will evaluate to the previous date in local time. That’s a nasty, silent bug waiting to happen.

To avoid this mess, always set the timezone explicitly when scheduling queries.
And more importantly, use @run_time instead of @run_date if you need to control the exact time, with timezone.

-- You don't need to wrap with DATE() unless you need a DATE type
DATE(@run_time, 'Asia/Seoul')
DATE(@run_time, 'Asia/Tokyo')

Unfortunately, many of the datamarts in our team were built using @run_date without timezone awareness, so now there’s a mountain of fixes ahead. But hey... we'll survive. Somehow. Ha!

Reference: https://qiita.com/sushi_edo/items/8250902ce2af778c2e8f