https://www.growthbook.io/ logo
#give-feedback
Title
# give-feedback
a

adventurous-agency-77764

08/21/2023, 12:43 PM
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
Copy code
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.
Copy code
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? 🙏
1
h

helpful-application-7107

08/21/2023, 4:09 PM
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.
If your metrics tables also have
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.
a

adventurous-agency-77764

08/21/2023, 4:20 PM
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! 🙌
h

helpful-application-7107

08/21/2023, 4:23 PM
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.
a

adventurous-agency-77764

08/21/2023, 4:24 PM
Works for me, happy to leave the queries like this 🙂 Thanks again!
🙌 1
a

agreeable-monitor-62727

08/22/2023, 8:02 PM
@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?
h

helpful-application-7107

08/22/2023, 10:53 PM
Yes, please do.
b

blue-telephone-40437

09/21/2023, 7:48 PM
@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?
8 Views