Connecting BigQuery and Google Sheets - DATE parameter issue

Question

following 1 I started creating a Spreadsheet which reads data from BigQuery, but I'm having an issue handling parameters related to date values.

In the first sheet, I created 2 cells with 2 parameters, the start and the end of a date interval, with proper values. Both cells are formatted as "Date" value.

In the second sheet I configured BigQuery connector, for this example, I'm using a public dataset with dates. bigquery-public-data.utility_eu.date_greg

From the BigQuery connector wizard I added:

"STARTDATE" as "PARAMETERS!B1"
"ENDDATE" as "PARAMETERS!B2"

After this configuration, this is the resulting query:

SELECT
  date,
  date_str,
  date_int

FROM `bigquery-public-data.utility_eu.date_greg` 

WHERE date > DATE(@STARTDATE) AND date < DATE(@ENDDATE)

LIMIT 10

I'm getting an error directly from the editor with this message:

> Error BigQuery: No matching signature for function DATE for argument types: INT64. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64) at [8:14]

As far as I can understand, the "date" cells are retrieved as a number, so the direct parse is not working. After a couple of tests, I understood the that given int value is the number I can obtain change cell format to "number". If you convert cell value from DATE to NUMBER you get this value:

01/05/2019 -> 43.586
31/05/2019 -> 43.616

What is this number? It is not milliseconds, it increases by 1 every next day. In order to create the proper query that can parse this int, I need to understand what is this int (of course I can handle the cell as "text" and writing the timestamp value directly, but I would prefer to have the native date format so I can use the built-in calendar.

My consideration (with simple math) is that this number refers to a number of days since 30/12/1899, but it is very odd (also, every date BEFORE this days is always 0), so I'm asking you directly how to handle this value. Basing on my understanding of when the number counter starts (30/12/1899), I created this query which add the number retrieved from the cell:

SELECT *

FROM `bigquery-public-data.utility_eu.date_greg`

WHERE 
  date >= DATE_ADD(DATE("1899-12-30"), INTERVAL @DATAINIZIO DAY)
  AND date <= DATE_ADD(DATE("1899-12-30"), INTERVAL @DATAFINE DAY)

It is working... but I think I'm doing a workaround that is not the proper way of doing this.

Also, is there any full documentation related to this BigQuery connection provided by Spreadsheet? Besides presentation in 1 I'm unable to find any specific documentation.

marc.fargas marc.fargas · Accepted Answer · 2019-07-11T15:03:48

Spreadsheets (Google, Excel, ...) store the dates as days passed since a starting date with a fractional day representing time.

From here: "Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time."

Now, you have to ways to filter by date on your Query:

In the query, you can use DATE_ADD to add your number of days (cell value) to the base date. (Carefull, DATE_ADD takes INT, and the date value is float so needs prior casting).
(preferred) on your spreadsheet you use TEXT(cell, "yyyy-mm-dd") so you can then use DATE() in the BigQuery query.

I use the second method as, though you need that extra cell (unless you directly store the date as YYYY-MM-DD; keeps the query cleaner than having a cast and date_add in there. Also would save you from the "1904 problem" explained in the link above.

Connecting BigQuery and Google Sheets - DATE parameter issue

3 Answers