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

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?

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?

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!

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.

Cheers

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.

1 Like

Guys,

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

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

Great news! Thanks!

Hi Izzy,

Is there some news about this?

Hi hi,

Our engineers agreed that this is something to be fixed— Specifically, they thought it has to do with the way that bigquery treats the word “datetime”. In a lot of dialects, it’s synonymous with timestamp, and we treat it as such. In BigQuery, though, it appears to be different, and that’s the hitch.

We’ve prioritized it, but not urgently, since there is a workaround (casting) that’s not too in depth— Which is to say it will get fixed, but there are currently other projects prioritized higher in this development cycle.

This topic is linked to the bug, so I’ll know if there’s any progress made and update here. Thanks for checking in!

Hi, the workaround works and isn’t too painful, but the bug isn’t very obvious and puzzled me for quite a while. So it’d be nice to have a fix.

Cheers,

Alex

A fix will definitely come, since it’s been triaged & prioritized by engineering. It’s just a matter of when, or if there are other things in the queue ahead of it. Thanks for checking in on it! I’ll drop a line where when it’s fixed.

Just adding a +1 in looking for a fix for this.

+1 From me as well!

As a bonus, you could do something like this:

TIMESTAMP(<datetime_column>, <database_timezone>)

(where <database_timezone> is the timezone specified in admin).

That way, you’d be enriching our datetime columns with timezone info! I think that’s actually a great value add, as well as a fix for an annoying bug. :grinning: