Potential bug in dimension type: date filter due to obligatory TIMESTAMP conversion

[Frederik1]

when using a dimension of type date,

dimension: utc_created_date {
type: date
sql: ${TABLE}.utc_created_date ;;
description: ""
}

when using as a dimension, this works as expected. When applying a filter on this column, i get the following error:

Query execution failed: - No matching signature for operator >= for argument types: DATE, TIMESTAMP. Supported signature: ANY >= ANY at [4:9]

This is caused by the fact that the filter value defaults to a timestamp, which means a comparison of a column of type DATE with a value of type TIMESTAMP:

WHERE ((( view_file.utc_created_date ) >= ((TIMESTAMP_TRUNC(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), WEEK(MONDAY)))) AND ( view_file.utc_created_date ) < ((TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), WEEK(MONDAY)), INTERVAL (1 * 7) DAY)))))

this can be fixed by casting the date field into a TIMESTAMP (BigQuery example):

dimension: utc_created_date {
type: date
sql: timestamp(${TABLE}.utc_created_date) ;;
description: ""
}

But generates to the following query that can be executed technically:

SELECT
(DATE(timestamp(view_name.utc_created_date) )) ASutc_created_date
FROM view_name
WHERE ((( timestamp(view_name.utc_created_date) ) >= ((TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -6 DAY))) AND ( timestamp(view_name.utc_created_date) ) < ((TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -6 DAY), INTERVAL 7 DAY)))))
GROUP BY 1

I see a potential issue for people who are querying from a different timezone:

(DATE(timestamp(view_name.utc_created_date) )) will not always be the same as view_name.utc_created_date in case the user is connecting using a different timezone.

When using date column filter, i expect to use the absolute value of the date in the timezone i ran that i prepared my data. I do not want Looker to convert this into a timestamp as this might lead to TimeZone specific results.

My proposal is that Looker will adapt the filter feature to apply the filter not as a timestamp but a date filter. This way, we can choose whether to have different results for different TZ users or we'd like to produce the exact same result as the aggregated table.

Thanks!

[Roderick]

Thanks for sharing! We appreciate you taking the time to draft such a thoughtful post! I'll make sure I share this with the LookML team. 

[communitymember]

Would this be better as a feature request? Or is this something that you can directly show to the LookML team?

[ogarza]

I just ran into the same problem, @Roderick any updates on a potential fix to be able to filter on dates dimensions?

[salehaxsid]

You will have to cast dates as suitable type as mentioned in above post because the LookML we write gets converted into SQL and then they are executed, so you will have to cast dimensions or measures or filters every time when you ran into datatype error.

[andy4]

Timezones are hard! Especially when your instance has "User-specific timezones" enabled, and you are simultaneously trying to prevent that from happening in certain instances.

My first tip is to find out whether you can simply turn off user-specific timezones. Looker will still attempt to convert timezones to a common one (instead of the database tz) in sometimes complex ways, so you won't be out of the woods but likely in a better place.

Regardless, you always have the option to add the "convert_tz: no" property to your date dimension or dimension_group. This will turn off all Looker timezone conversion for that field. I often use this when I have an NDT where i've included a already-converted field that i need to keep in datetime (once the time is stripped and the field is converted to a date this usually stops being a problem).

You may also look into the "datatype" property. If your db column is a type other than datetime (the default), changing that property to match how your data is stored will often improve unexpected behavior.

But, my main advice is to keep testing and iterating until you got it right. Timezones are hard! Which is why James and I wrote another article giving a rundown of Looker's various timezone related features here: How Looker does timezones and how to troubleshoot them

Let us know how it goes!