How to query Google BigQuery TIMESTAMP column as DATETIME

nayan · March 21, 2024, 1:48pm

PROBLEM

You are using the Google BigQuery Connector and you need to query the Google BigQuery TIMESTAMP datatype column as DATE or DATETIME and you cannot change BigQuery table schema.

SOLUTION

You have to prefix SQL with #DirectSQL, and then you can use any valid SQL supported by Google BigQuery. Use the CAST function to convert the UNIX TIMESTAMP column to DATE or DATETIME:

#DirectSQL

SELECT 
   ColInteger,
   CAST(ColTimestamp AS DATETIME) AS ColTimestamp_AsDate,
   ColTimestamp,
   ColDatetime,
   ColDate
FROM TestDataset.DataTypeTest
WHERE ColTimestamp IS NOT NULL
LIMIT 10