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

[vfx1]

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?

[fabio1]

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?

[fabio1]

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!

[vfx1]

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

[vfx1]

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.

[vfx1]

Guys,

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

[izzymiller]

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

[vfx1]

Great news! Thanks!

[vfx1]

Hi Izzy,

Is there some news about this?

[izzymiller]

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!

[izzymiller]

bug: https://looker.atlassian.net/browse/MOD-447

[alexanderm]

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

[izzymiller]

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.

[RobG]

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

[jtopp]

+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. 😀

[Kate_Fray]

I have found that when trying to create a time dimension group parameter based on a date type that is atypical (not based on a datetime field), the best way to overcome this issue is to apply a datatype parameter as such:

dimension_group: created {
type: time
**datatype: date**
timeframes: [
  raw,
  time,
  date,
  week,
  month,
  quarter,
  year
]
sql: ${TABLE}._created_date ;;

}

This alleviates the error above.

[vfx1]

Hi Kate,

It goes half way there 🙂 Doing the above makes looker to write the following:

CAST(CAST(<some_datetime> AS TIMESTAMP) AS DATE)

It’s great as:

• doesn’t break

It’s not so great as:

• removes the time from the datetime
• only works in UTC - in every other timezone produces potentially wrong date

So all in all - doesn’t seem to be a workaround for the issue.

[izzymiller · 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!

[mwalle]

This was the only thread I could find with any mention of 7.14 at this time, so I’m hijacking it just to ask @izzymiller if you know when the 7.14 relnotes will be published to https://docs.looker.com/relnotes

It’s July 30th which is the expected final release date for 7.12; but there are fixes we’ve been informed of in 7.14 that we’d love to see the published details of.

[Jason_Comp_Thre]

@izzymiller didn’t see this feature in 7.14. Am I missing it or did it get bumped to the next release? Thanks!

[jesse_carah]

@Jason_Comp_Thre . This was indeed addressed in 7.14. See the changelog here:

Fixed: Incorrect SQL for datetime fields for BigQuery dialects is no longer being generated.