Dimension_group w/ type "time" doesn't work with datatype: datetime for BigQuery

vfx1
New Member

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?

Solved Solved
1 20 3,940
1 ACCEPTED SOLUTION

You’ll be pleased to know (especially @vfx1) that we’ve just fixed this issue. It should be present in the next version of Looker.

We’ll be introducing a legacy feature flag as well as a model-level keyword parameter to control this, to make sure that folks can get the proper experience without breaking any code that has been relying on this incorrect behavior.

I’m being intentionally vague as there’s still time for things to change, so keep your eyes peeled for the details in the release notes for 7.14!

View solution in original post

20 REPLIES 20
Top Labels in this Space
Top Solution Authors