Passing Dynamic Date Variable in Big Query| Python-CodePudding

I'm trying to run this bigQuery [in python]:

  start_date= "2022-03-08"
  end_date = "2022-05-03"
  query = f"""
    SELECT SUM(CostInBillingCurrency) as cost,EXTRACT(MONTH from date) as MONTH, SubscriptionId, SubscriptionName
    FROM `table_name`
    WHERE
    Date BETWEEN TIMESTAMP({start_date})
    AND TIMESTAMP({end_date})
    GROUP BY
    SubscriptionId, SubscriptionName,MONTH """

this is the error I'm getting:

google.api_core.exceptions.BadRequest: 400 No matching signature for function TIMESTAMP for argument types: INT64. Supported signatures: TIMESTAMP(STRING, [STRING]); TIMESTAMP(DATE, [STRING]); TIMESTAMP(DATETIME, [STRING]); TIMESTAMP(TIMESTAMP) at [5:18]

If I hardcode the date as:

  Date BETWEEN TIMESTAMP("2022-06-01")
  AND TIMESTAMP("2022-06-30")

it works fine. so the query is right.

please help in figuring out how to pass date dynamically.

CodePudding user response：

You have to add quotes. If you do result = f'TIMESTAMP({start_date})'it renders to TIMESTAMP(2022-06-01). So change it to result = f'TIMESTAMP("{start_date}") or result = f"TIMESTAMP('{start_date}')" and you should be good.

CodePudding user response：

Seems like the the variable is just putting values in the query, but query is expecting the string, you update your query as follows

query = f"""
SELECT SUM(CostInBillingCurrency) as cost,EXTRACT(MONTH from date) as MONTH, SubscriptionId, SubscriptionName
FROM `table_name`
WHERE
Date BETWEEN TIMESTAMP("{start_date}")
AND TIMESTAMP("{end_date}")
GROUP BY
SubscriptionId, SubscriptionName,MONTH """