Consider the following code:
view: datetime {
derived_table: {
sql: SELECT CURRENT_DATETIME as dt;; # Bigquery dialect
}
dimension_group: dt {
type: time
timeframes: [
date,
month
]
sql: ${TABLE}.dt ;;
datatype: datetime
}
}
explore: datetime {}
This is supposed to be pretty straightforward - a dimension group with datetime as underlying data type.
Looker generates the following SQL:
WITH datetime AS (SELECT CURRENT_DATETIME as dt)
SELECT
CAST(TIMESTAMP(FORMAT_TIMESTAMP('%F %T', datetime.dt , 'Asia/Kolkata')) AS DATE) AS datetime_dt_date
FROM datetime
GROUP BY 1
Which produces an error as the underlying type is not timestamp.
Failed to retrieve data - No matching signature for function FORMAT_TIMESTAMP for argument types: STRING, DATETIME, STRING. Supported signature: FORMAT_TIMESTAMP(STRING, TIMESTAMP, [string]) at [4:24]
This happens with other datatypes too. There is a way to overcome it by casting the datetime to timestamp in the ‘sql:’ element but this is a hack and produces redundant transformation.
Is it a bug?
Best answer by izzy
View original