Consider the following Bigquery View:
SELECT CURRENT_DATETIME as dt
It produces a single column of type datetime
Then use “create view from table” to produce the view code:
It produces the following code:
view: test_datetime {
sql_table_name: views.test_datetime ;;
dimension_group: dt {
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: CAST(${TABLE}.dt AS TIMESTAMP) ;;
}
measure: count {
type: count
drill_fields: []
}
}
CAST(${TABLE}.dt AS TIMESTAMP)
is actually incorrect if the application timezone is not UTC. This cast assumes the the timezone is UTC. One way to fix it is to use:
TIMESTAMP(${TABLE}.dt, "{{ _query._query_timezone }}")
which is going to produce the right timestamp.
Additional question is why at all the conversion is needed? It can be kept as datetime
.
Do you consider the above behavior a bug?
Thanks