sql - DATE_DIFF 计算 BigQuery 中两个日期之间的差异

Question

我正在尝试计算 BigQuery 中两个日期之间的秒数差异。

建议的语法是:

SELECT DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",DATETIME "2008-12-25 15:30:00", SECOND)

这工作正常。

但是，当我尝试使用存储的字段时，语法的工作方式不同:

with dates as (
SELECT
"2010-07-07 10:20:00" as date1,
 "2008-12-25 15:30:00" as date2
)
SELECT
   DATETIME_DIFF(DATETIME (date2), DATETIME (date1), SECOND)
FROM dates

我确定这是一个简单的错误，但想知道为什么这不能按预期工作？

Answer 1

下面是 BigQuery 标准 SQL

#standardSQL
WITH dates AS (
  SELECT "2010-07-07 10:20:00" AS date1, "2008-12-25 15:30:00" AS date2
)
SELECT DATETIME_DIFF(DATETIME(TIMESTAMP(date2)), DATETIME(TIMESTAMP(date1)), SECOND)
FROM dates   

修复很明显，在上面的代码中可以清楚地看到

另外一个选项:

#standardSQL
WITH dates AS (
  SELECT "2010-07-07 10:20:00" AS date1, "2008-12-25 15:30:00" AS date2
)
SELECT TIMESTAMP_DIFF(TIMESTAMP(date2), TIMESTAMP(date1), SECOND)
FROM dates