Passing Dynamic Date Variable in Big Query| Python

Question

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.

Simon Hawe · Accepted Answer · 2022-07-28 11:30:58Z

2

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.

answered Jul 28, 2022 at 11:30

Simon Hawe

4,63913 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pradeep Yenkuwale · Accepted Answer · 2022-07-28 11:34:54Z

1

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 """

answered Jul 28, 2022 at 11:34

Pradeep Yenkuwale

3185 silver badges14 bronze badges

Collectives™ on Stack Overflow

Passing Dynamic Date Variable in Big Query| Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related