adventurous-agency-77764
08/21/2023, 12:43 PMwhere
clause in the experiment queries from Growthbook Cloud that emerged sometime over the weekend. Currently, the query compiles to
WHERE
e.experiment_id = 'hybrid_and_it2'
AND e.timestamp >= '2023-08-17 15:47:00'
AND e.timestamp <= '2023-08-21 12:33:48'
With this query, I get the error Could not cast literal "2023-08-17 15:47:00" to type DATE at [25:26]
. The date ‘2023-08-17’ is the start date of the experiment.
If I copy the query into BigQuery and edit the query above with datetime('2023-08-17 15:47:00')
, I no longer get the error.
WHERE
e.experiment_id = 'hybrid_and_it2'
AND e.timestamp >= datetime('2023-08-17 15:47:00')
AND e.timestamp <= datetime('2023-08-21 12:33:48')
Would someone be able to help support to solve? 🙏helpful-application-7107
08/21/2023, 4:09 PMCAST
statements because it was causing sub-optimal performance when people had tables partitioned by a TIMESTAMP field rather than a DATETIME field.
The error is happening because the timestamp
column you pass us is of type DATE
and we use a string literal with hours to build the date range.
We want to be able to support the case where timestamp
is a DATE
, especially in the metrics source, where maybe this filter is less critical and just used to improve performance, so we will work on that ASAP.
One potential solution in the meantime would be to CAST(timestamp AS DATETIME) as timestamp
in your Experiment Assignment query. In that case, our filters should work just as before.timestamp
that is of type DATE
, you may also need to cast those to DATETIME
to get it to work, at least until we land a fix from our end.adventurous-agency-77764
08/21/2023, 4:20 PMhelpful-application-7107
08/21/2023, 4:23 PMDATE
fields. Because we allow you to set start and end dates for an experiment using hours (and minutes) and because you can set conversion windows down to the hour, we really are built to work with DATETIME
or TIMESTAMP
data fields. When you work with DATE
you lose some of that fidelity.adventurous-agency-77764
08/21/2023, 4:24 PMagreeable-monitor-62727
08/22/2023, 8:02 PMhelpful-application-7107
08/22/2023, 10:53 PMblue-telephone-40437
09/21/2023, 7:48 PM