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.