Upcast date + duration and date - duration operations to datetime

[henryharbeck]

Description

It seems to be generally accepted in databases that date + duration or date - duration should return a datetime (or timestamp).
I have tested BigQuery, DuckDB and PostgreSQL and found them all to follow this.

This feature request is to implement the same in Polars.

I'm unsure whether this is best categorised as a bug fix due to silent loss of precision when (for example) adding 1 hour to a date (as below), or a (potentially breaking) feature.

from datetime import date
import duckdb
import polars as pl
print(
    pl.DataFrame({"d": date(2024, 1, 1)})
    .with_columns(
        dt=pl.col("d") + pl.duration(hours=1),
        dt2=pl.col("d") + pl.duration(days=1),
    )
)
# shape: (1, 3)
# ┌────────────┬────────────┬────────────┐
# │ d          ┆ dt         ┆ dt2        │
# │ ---        ┆ ---        ┆ ---        │
# │ date       ┆ date       ┆ date       │
# ╞════════════╪════════════╪════════════╡
# │ 2024-01-01 ┆ 2024-01-01 ┆ 2024-01-02 │
# └────────────┴────────────┴────────────┘

print(duckdb.sql(
    """
    FROM (VALUES (DATE '2024-01-01')) df(d)
    SELECT
        *,
        d + INTERVAL '1 HOUR' AS dt,
        d + INTERVAL '1 DAY' AS dt2,
    """
))
# ┌────────────┬─────────────────────┬─────────────────────┐
# │     d      │         dt          │         dt2         │
# │    date    │      timestamp      │      timestamp      │
# ├────────────┼─────────────────────┼─────────────────────┤
# │ 2024-01-01 │ 2024-01-01 01:00:00 │ 2024-01-02 00:00:00 │
# └────────────┴─────────────────────┴─────────────────────┘

[MarcoGorelli]

thanks for opening the issue!

This is similar to what happens for datetime / duration of different time_units:

• datetime[us] + duration[ms] => datetime[ms]

and the loss of information can already happen there:

In [4]: df
Out[4]:
shape: (1, 1)
┌────────────────────────────┐
│ a                          │
│ ---                        │
│ datetime[μs]               │
╞════════════════════════════╡
│ 2020-01-01 00:00:00.123456 │
└────────────────────────────┘

In [5]: df.with_columns(b=pl.col('a') + pl.duration(hours=1, time_unit='ms'))
Out[5]:
shape: (1, 2)
┌────────────────────────────┬─────────────────────────┐
│ a                          ┆ b                       │
│ ---                        ┆ ---                     │
│ datetime[μs]               ┆ datetime[ms]            │
╞════════════════════════════╪═════════════════════════╡
│ 2020-01-01 00:00:00.123456 ┆ 2020-01-01 01:00:00.123 │
└────────────────────────────┴─────────────────────────┘

This was discussed previously, in #12023

So I guess the remaining question is - should date behave differently, and just raise instead of losing information?

[mcrumiller]

Recall we updated the mean/median of a date array to return a datetime. The analogue here was treating date/datetime like int/float, which is fairly natural. For this reason, I would think that just like 1 + 1.5 = 2.5, date(2025, 1, 1) + timedelta(hours=12) should return datetime(2025, 1, 1, 12), although that's not what happens for python's date/datetime package.