ibis.timestamp(...) becomes DATETIME(...) in BigQuery, but I'd want TIMESTAMP(...)

[astrojuanlu]

I'm trying to recreate this query in Ibis:

SELECT
  *
FROM
  `bigquery-public-data.pypi.file_downloads`
WHERE
  project = 'kedro'
  AND TIMESTAMP("${runtime_params:start_date, ${globals:start_date}}") <= timestamp
  AND timestamp < TIMESTAMP("${runtime_params:end_date, ${globals:end_date}}")
LIMIT 5

So I did this:

ibis.to_sql(
    pypi_kedro_raw_lazy
    .filter([
        pypi_kedro_raw_lazy.project == "kedro",
        ibis.timestamp("2024-07-01") <= pypi_kedro_raw_lazy.timestamp,
        pypi_kedro_raw_lazy.timestamp < ibis.timestamp("2024-07-02"),
    ])
    .head(5)
)

which returns:

SELECT
  *
FROM `bigquery-public-data`.`pypi`.`file_downloads` AS `t0`
WHERE
  `t0`.`project` = 'kedro'
  AND DATETIME('2024-07-01T00:00:00') <= `t0`.`timestamp`
  AND `t0`.`timestamp` < DATETIME('2024-07-02T00:00:00')
LIMIT 5

But this query is invalid:

No matching signature for operator <= for argument types: DATETIME, TIMESTAMP. Supported signature: ANY <= ANY at [5:3]

It turns out in this case it works without converting to TIMESTAMP just because I happen to use an appropriate string format I guess. But how should I do in cases like these?

[gforsyth]

Hi @astrojuanlu -- get ready for a feeling of deep dread.

In BigQuery, DATETIMEs have no time zone, TIMESTAMPs have a time zone.
If you use ibis.timestamp and don't specify a time zone, when it compiles to BigQuery, will give you a DATETIME.
So, if you want TIMESTAMP, make sure you specify a time zone.

Time was a mistake.

[astrojuanlu]

laughs in temps-coordonnée barycentrique

[astrojuanlu]

Also, thank you!!

[astrojuanlu]

It works

In [12]: print(ibis.to_sql(
    ...:     pypi_kedro_raw_lazy
    ...:     .filter([
    ...:         pypi_kedro_raw_lazy.project == "kedro",
    ...:         ibis.timestamp("2024-07-01", timezone="UTC") <= pypi_kedro_raw_lazy.timestamp,
    ...:         pypi_kedro_raw_lazy.timestamp < ibis.timestamp("2024-07-02", timezone="UTC"),
    ...:     ])
    ...:     .head(5)
    ...: ))
SELECT
  *
FROM `bigquery-public-data`.`pypi`.`file_downloads` AS `t0`
WHERE
  `t0`.`project` = 'kedro'
  AND TIMESTAMP('2024-07-01T00:00:00+00:00') <= `t0`.`timestamp`
  AND `t0`.`timestamp` < TIMESTAMP('2024-07-02T00:00:00+00:00')
LIMIT 5