CODEWITHSUNDEEP
sql-servergoogle-bigquery
Mark Schindler
Mark Schindler

Reputation: 13

How to generate an unnested array in BigQuery using existing timestamp

I'd like to take this query (which works for me): 

SELECT(DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL 1* n MONTH)) as date
FROM UNNEST(GENERATE_ARRAY(0,12,1)) n

And replace the CURRENT_TIMESTAMP() with a start_date timestamp field from an existing table.

In other words, I'd like to create a list of "active months" for a contract that begins on start_date.

I'm getting stuck on how to incorporate another FROM clause!

Upvotes: 1

Views: 538

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

This should work:

SELECT(DATE_ADD(DATE(start_date), INTERVAL 1* n MONTH)) as date
FROM ExistingTable, UNNEST(GENERATE_ARRAY(0,12,1)) n

If you have exactly one row in the existing table representing the start date, you can use a subselect:

SELECT(DATE_ADD(DATE((SELECT start_date FROM ExistingTable)), INTERVAL 1* n MONTH)) as date
FROM UNNEST(GENERATE_ARRAY(0,12,1)) n

Upvotes: 1

Related Questions