时间戳

首页 > 解决方案 > 连接 BigQuery 和 Google 表格 - 日期参数问题

date - 连接 BigQuery 和 Google 表格 - 日期参数问题

问题描述

在1之后，我开始创建一个从 BigQuery 读取数据的电子表格，但是在处理与日期值相关的参数时遇到了问题。

在第一张表中，我创建了 2 个具有 2 个参数的单元格，即日期间隔的开始和结束，并具有适当的值。两个单元格都被格式化为“日期”值。

在第二张表中，我配置了 BigQuery 连接器，在本示例中，我使用的是带有日期的公共数据集。bigquery-public-data.utility_eu.date_greg

从我添加的 BigQuery 连接器向导中：

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

在此配置之后，这是生成的查询：

SELECT
  date,
  date_str,
  date_int

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

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

LIMIT 10

我直接从编辑器收到一条错误消息：

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

据我了解，“日期”单元格是作为数字检索的，因此直接解析不起作用。经过几次测试，我了解到给定的 int 值是我可以获得的数字，将单元格格式更改为“数字”。如果将单元格值从 DATE 转换为 NUMBER，则会得到以下值：

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

这个数字是多少？它不是毫秒，它每隔一天增加 1。为了创建可以解析这个 int 的正确查询，我需要了解这个 int 是什么（当然我可以将单元格作为“文本”处理并直接写入时间戳值，但我更喜欢使用原生日期格式所以我可以使用内置日历。

我的考虑（用简单的数学）是这个数字是指自 以来的天数30/12/1899，但它很奇怪（而且，这天之前的每个日期总是 0），所以我直接问你如何处理这个值。根据我对数字计数器何时开始（1899 年 12 月 30 日）的理解，我创建了这个查询，它添加了从单元格中检索到的数字：

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)

它正在工作......但我认为我正在做一个不正确的解决方法。

此外，电子表格是否提供与此 BigQuery 连接相关的完整文档？除了1中的演示文稿外，我找不到任何具体的文档。

标签： dategoogle-sheetsgoogle-bigquery

解决方案

电子表格（Google、Excel 等）将日期存储为自开始日期以来经过的天数，并用小数天表示时间。

从这里：“Excel 将日期和时间存储为一个数字，表示自 1900 年 1 月 0 日以来的天数，加上 24 小时日的小数部分：ddddd.tttttt。这称为序列日期或序列日期时间。”

现在，您必须在查询中按日期过滤：

  1. 在查询中，您可以使用 DATE_ADD 将天数（单元格值）添加到基准日期。（小心，DATE_ADD 采用 INT，日期值是浮点数，因此需要预先转换）。
  2. （首选）在您的电子表格上使用 TEXT(cell, "yyyy-mm-dd") 以便您可以在 BigQuery 查询中使用 DATE()。

我使用第二种方法，尽管您需要那个额外的单元格（除非您直接将日期存储为 YYYY-MM-DD；保持查询比在其中使用 cast 和 date_add 更清晰。也可以让您免于“1904 问题”在上面的链接中解释。

推荐阅读