Bad SQL generated from Explore

[skorpio11-16412]

Background

Creating and Explore with an “is in the past” 10 days filter generates the following where clause

WHERE ((( CAST(profiles.date as date)  ) >= ((TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY))) AND ( CAST(profiles.date as date)  ) < ((TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY), INTERVAL 10 DAY)))))

Which generates an

invalidQuery: No matching signature for operator >= for argument types: DATE, TIMESTAMP. Supported signature: ANY >= ANY at [6:9]  error message.

Using SQL runner, casting the timestamp output to Date as follows:

WHERE ((( CAST(profiles.date as date)  ) >= date((TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY))) AND ( CAST(onn_profiles.date as date)  ) < date((TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY), INTERVAL 10 DAY)))))

Corrects the error.

Question

What is the best way to correct the auto generated SQL from looker to avoid comparing Date with Datetime?

[n_davis]

You could use the sql in a yes no field in lookml, and then use that yes/no field as a filter.

[leobardor · Accepted solution]

Hi Skorpio.

Hope you are doing great.

I suggest the following steps to achieve this dimension :

1.- Identify the original datatype of the column of the database 

(SQL Dialect, SQL datatype)

2.- CAST to date in the dimension group according to your SQL dialect.

Sharing LookML definition, SQL datatype and SQL dialect will help.

Regards!

Leo