Hello! Thanks for Growthbook, we really enjoy the product! ☀️ But, I think that there’s a bug with the

where

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? 🙏

Hi Alice. Sorry about this, we recently rolled out a change that removed some

CAST

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.

Hey Luke! Thanks so much for the quick response - I took your advice and casted the timestamps as datetimes in the assignment queries and the metrics tables and it works great! Thanks so much! 🙌

Awesome! If that works for you, that's our preferred solution given your data has

DATE

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.

Works for me, happy to leave the queries like this 🙂 Thanks again!

@helpful-application-7107 Thank you for the answer! I'm facing the same problem, i'll try this solution, and if it doesn't work i send on this thread, ok?

Yes, please do.

@helpful-application-7107 this did not solved the problem. I'm using a query like this: SELECT user_pseudo_id AS user_id, TIMESTAMP_MICROS(event_timestamp) AS timestamp, SUM( IF ( event_name = 'Clique Conheça mais sobre nós', 1, 0 ) ) AS ClickConheca, user_pseudo_id AS anonymous_id FROM`extratores.growthbook_us.growthbook_us`WHEREevent_name = 'Clique Conheça mais sobre nós'GROUP BY user_id, timestamp, anonymous_id And i'm not seeing the results in my experiment. Are u avaliable for a meet? Or may i send prints for u in private?