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. 

[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.