Search code examples
google-bigquerylooker-studio

Use YEAR + MONTH as date in Data Studio

I'm using Google BigQuery as a database and Data Studio to visualize my data. Now in order to reduce the row numbers to be processed, I want to aggregate my DAILY data in BigQuery to MONTHLY. How should I do this, so that Data Studio still understands it's a date?

I have tried SELECT FORMAT_DATE('%Y%m', date) as year_month, but FORMAT_DATE returns a string, and Data Studio doesn't seem to be able to interpret it as a month any more.

My ultimate goal is to use the YEAR MONTH in a Column chart as the date on the x-axis. So I need to have Data Studio interpret it as a date, because I want to be able to drill up from monthly to yearly.

Note: I now that I could also use the daily data in Data Studio, and format it as "Year Month", but the key here is to reduce the number of rows from som 40M rows to 1/30 of that.

Any hints are greatly appreciated!

Solution

  • Use DATE_TRUNC():

    SELECT DATE_TRUNC(date, month) as year_month

    This returns midnight at the beginning of the first day of the month (well, there is no time on dates but the corresponding datetime and timestamp versions have the midnight time).