Google BigQuery中UNIX_DATE函数报错求助：INT64参数不兼容

Let's break down why you're hitting this error and how to fix it quickly.

The Root Cause #

The UNIX_DATE() function in BigQuery requires a DATE type as input, but your created_utc field is stored as INT64—which is a Unix timestamp (count of seconds since the epoch, standard for Reddit's data). Directly passing an integer to UNIX_DATE() throws that signature mismatch error because the function can't interpret raw integers as calendar dates.

The Solution #

You need to convert the integer timestamp to a DATE first, following these steps:

• Convert the INT64 timestamp to a TIMESTAMP using TIMESTAMP_SECONDS() (since Reddit's created_utc uses second-level timestamps).
• Convert that TIMESTAMP to a DATE using the DATE() function.
• Finally, pass the DATE value to UNIX_DATE().

Here's the corrected query:

SELECT UNIX_DATE(DATE(TIMESTAMP_SECONDS(created_utc))) 
FROM `fh-bigquery.reddit_comments.2017_08`

Quick Edge Case Note #

If for some reason your created_utc timestamp uses milliseconds (uncommon for Reddit data, but possible in other datasets), replace TIMESTAMP_SECONDS() with TIMESTAMP_MILLIS() instead.

Why Your Previous Conversion Attempts Might Have Failed #

If you tried something like CAST(created_utc AS DATE), that won't work—BigQuery interprets the integer as a count of days since the epoch (not seconds). The TIMESTAMP_SECONDS() → DATE() flow properly translates the timestamp to a calendar date before feeding it to UNIX_DATE().

内容的提问来源于stack exchange，提问作者kkim