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!