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


(vfx) #1

Consider the following code:

view: datetime {
  derived_table: {
    sql: SELECT CURRENT_DATETIME as dt;; # Bigquery dialect

  dimension_group: dt {
    type: time
    timeframes: [
    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:

	CAST(TIMESTAMP(FORMAT_TIMESTAMP('%F %T', datetime.dt , 'Asia/Kolkata')) AS DATE) AS datetime_dt_date
FROM datetime


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?

Create view from table does not consider timezone for datetime fields (BigQuery)
Create view from table does not consider timezone for datetime fields (BigQuery)
(Fabio) #2

I think this is just a discrepancy in nomenclature. “Datetime” in LookML represents a concept which may have different names in different SQL dialects.

On the other hand, the “datetime” type in BigQuery is actually rather clunky and difficult to work with - it represents a local time with no timezone information, which is (in my experience) not so useful in practice. is there a reason you’re not using a BigQuery timestamp instead?

(Fabio) #3

Looking at our docs a bit more, I guess we have LookML datatypes of both datetime and timestamp. I think I’ll have to read into it a bit more!

(vfx) #4

Hi Fabio,

Thanks for taking the time to look at this. The reason why we are using DATETIME datatype is because the underlying Bigquery tables use fields of this type and they come from the original data sources.

Of course DATETIME fields can be converted back to timestamp while creating the views in Looker but then what is the use for the “datatype” parameter if it has to be converted to TIMESTAMP?

On a side note, the automatic conversion to TIMESTAMP (by using create view from table) also doesn’t seem to work properly with timezones, so I reported this separately as a potential bug here.


(vfx) #5

I found more details.

The documentation mentions at least at two places that for dimension_group of type time the default datatype is datetime.

The default value for datatype is datetime.

By observing how looker writes SQL it seems the assumed default type (at least for BigQuery) is in fact timestamp. And even if datatype parameter is specified to be datetime it’s being ignored, which produces invalid SQL.

(vfx) #6


I was just wondering if there is some progress on this. It seems somewhat foundational to me…

(Izzy) #7

I think you’re right and that this is an issue with our default generated SQL— I’ll pass it along to engineering!

(vfx) #8

Great news! Thanks!