https://www.growthbook.io/ logo
Join Slack
Powered by
Hey there, is there a way to declare variables in ...
# ask-questions
c
Hey there, is there a way to declare variables in metric sql for a big query data source?
h
No, because we use the SQL you specify within sub-queries and CTEs with other SQL, so it has to be a single table statement. What are you hoping to achieve?
c
Im hoping to change the built-in handlebars startDateISO and endDateISO from UTC to PST since my BigQuery data source is PST.
Copy code
FORMAT_DATE("%Y%m%d", DATE(TIMESTAMP_SECONDS({{date startDateISO "t"}}), "America/Los_Angeles"))
Then use that in the WHERE clause that limits what tables are queried based on _TABLE_SUFFIX
Copy code
AND ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
       (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
My BigQuery data source has a lot of datasets since I am running experiments across multiple domains, each with their own GA linked to the BigQuery project, generating a dataset for each.
It would be nice to have that FORMAT_DATE code for startDate and endDate in one place instead of like 10.
fun...
Copy code
SELECT
  gbuuid_param.value.string_value AS gbuuid,
  TIMESTAMP_MICROS(event_timestamp) AS timestamp
FROM
  `stream-p`.`analytics_301842526`.`events_*` AS watchnow_one,
  UNNEST(event_params) AS gbuuid_param
WHERE
  event_name = '{{eventName}}'
  AND gbuuid_param.key = 'gbuuid'
  AND gbuuid_param.value.string_value is not null
  AND (
    (
      _TABLE_SUFFIX BETWEEN FORMAT_DATE(
        '%Y%m%d',
        DATE(
          TIMESTAMP_SECONDS({{date startDateISO "t"}}),
          "America/Los_Angeles"
        )
      ) AND FORMAT_DATE(
        '%Y%m%d',
        DATE(
          TIMESTAMP_SECONDS({{date endDateISO "t"}}),
          "America/Los_Angeles"
        )
      )
    )
    OR (
      _TABLE_SUFFIX BETWEEN CONCAT(
        'intraday_',
        FORMAT_DATE(
          '%Y%m%d',
          DATE(
            TIMESTAMP_SECONDS({{date startDateISO "t"}}),
            "America/Los_Angeles"
          )
        )
      ) AND CONCAT(
        'intraday_',
        FORMAT_DATE(
          '%Y%m%d',
          DATE(
            TIMESTAMP_SECONDS({{date endDateISO "t"}}),
            "America/Los_Angeles"
          )
        )
      )
    )
  )
h
I see. Yeah I think this has to be done manually at the moment as it seems like you're currently doing it 😕
maybe we could build a helper handlebars function that does timezone stuff to at least make it a little less painful? Or have some other setting? We could potentially also allow for declaring variables at the top of every query in a datasource. (e.g. you specify it in one place and we add it to every SQL statement we generate)
c
that would be great, and probably make sense assuming my use case is common where the data source isnt UTC
maybe its not common 🤷‍♂️
{{date startDateISO "yyyyyMMdd" "America/Los_Angeles"}} 😉
h
Is the core issue that we're filtering out data from the first day of an experiment if the experiment launches in the last ~8 hours of the day? Because our experiment startDateISO is in UTC?
c
yes
that is exactly the issue
startDateISO renders to tomorrow when BigQuery still thinks its today
so, BigQuery has not yet created an intraday table that matches the UTC day
image.png
h
startDateISO renders to tomorrow
Like when you just start an experiment right?
c
startDateISO always renders to the UTC date. so, for the last 7 or 8 hours of the day (depending on daylight savings), there is no result data that matches the UTC date when BigQuery is operating in PST.
h
Got it. Another solution is to just subtract a day from the start date but that should be less performant and require basically as much work as what you're already doing.
c
right, i saw that suggestion in a previous growthbook chat where the person wasnt getting any data yet (likely due to the same timezone issue im trying to resolve)
i thought it would be fun to try adjusting by exact timezone since im still learning sql and that would be a good exercise.
🙌 1
h
Yeah, just confirmed that the experiment start dates on our end are UTC.
Ok, well there's no easy solution but I'll note our discussion here as some improvements we could make to auto-correct timestamps with, hopefully, a simple datasource setting. Unlikely to happen in the near term but we'll keep an eye on it.
c
sounds good, thanks for your time and attention.
78 Views
Open in Slack
PreviousNext
Powered by