In my source explore I have a field called History Date, that is of type date. Then I want to create NDT out of it using:
column: history_date {
field: my_explore.history_date
}
This field has datatype:date + convert_tz: no in its definition but the SQL code for an NDT shows the following”
(TIMESTAMP(my_explore.date )) AS date,
Then, the problem occurs when filtering. it creates a mismatch between TIMESTAMP and a DATE.
my_explore.date ) >= ((DATE_ADD(DATE_TRUNC(CURRENT_DATE('America/New_York'), MONTH), INTERVAL -2 MONTH))) AND ( my_explore.date ) < ((DATE_ADD(DATE_ADD(DATE_TRUNC(CURRENT_DATE('America/New_York'), MONTH), INTERVAL -2 MONTH), INTERVAL 3 MONTH)))))
Looks to me like the field type of the field is correctly inferred from the LookML because the filters are DATE_ADD not TIMESTAMP_ADD, but the conversion in the NDT is totally wrong here.
Current workaround: create date column as Derived Column and cast it to DATE
LinkedIn
Twitter
