No translation rule for date/datetime diff.

[jcmincke]

Substracting 2 dates or 2 timestamp fails:

import ibis
import ibis_bigquery
from google.cloud import bigquery
import pandas as pd
import datetime as dt

my_table = 'my_table'

# pdf = pd.DataFrame({'date': [dt.date(2020, 1, 1), dt.date(2021, 5, 15), dt.date(2021, 7, 9)]})

pdf = pd.DataFrame({'date': [dt.datetime(2020, 1, 1), dt.datetime(2021, 5, 15), dt.datetime(2021, 7, 9)]})

# Load client
client = bigquery.Client(project=my_project)

# Load data to BQ
client.delete_table(my_dataset + "." + my_table, not_found_ok=True)
job = client.load_table_from_dataframe(pdf, my_dataset + "." + my_table)

ibis.options.interactive = False

conn = ibis_bigquery.connect(
    project_id=my_project,
    dataset_id=my_dataset)

t = conn.table(my_table)

e1 = t.mutate(z=t.date.sub(t.date))

print(ibis.bigquery.compile(e1))

And here is the exception:

Traceback (most recent call last):
  File "<input>", line 3, in <module>
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/bigquery/__init__.py", line 38, in compile
    return to_sql(expr, dialect.make_context(params=params))
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/bigquery/compiler.py", line 86, in to_sql
    compiled = query_ast.compile()
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 55, in compile
    compiled_queries = [q.compile() for q in self.queries]
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 55, in <listcomp>
    compiled_queries = [q.compile() for q in self.queries]
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 1650, in compile
    select_frag = self.format_select_set()
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 1705, in format_select_set
    expr_str = self._translate(expr, named=True)
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 1598, in _translate
    return translator.get_result()
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 1362, in get_result
    translated = self.translate(self.expr)
  File "/Users/V3/windev/carrefour/gcp/venv/lib/python3.9/site-packages/ibis/backends/base_sqlalchemy/compiler.py", line 1403, in translate
    raise com.OperationNotDefinedError(
ibis.common.exceptions.OperationNotDefinedError: No translation rule for <class 'ibis.expr.operations.TimestampDiff'>

[jcmincke]

Difference between times also fails.

[toryhaavik]

there's nothing stopping us from implementing this, right? https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_diff, https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_diff, and https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_diff