[C++] Date and datetime types should autocast from strings

[asfimport]

When comparing dates and datetimes, people frequently expect that a string (formatted as ISO8601) will auto-cast and compare to dates and times.

Examples in R:

library(arrow)
#> 
#> Attaching package: 'arrow'
#> The following object is masked from 'package:utils':
#> 
#>     timestamp

arr <- Array$create(as.Date(c("1974-04-06", "1988-05-09")))
arr > "1980-01-01"
#> Error: NotImplemented: Function greater has no kernel matching input types (array[date32[day]], scalar[string])

# creating the scalar as a date works, of course
arr > Scalar$create(as.Date("1980-01-01"))
#> Array
#> <bool>
#> [
#>   false,
#>   true
#> ]

# datatimes also do not auto-cast
arr <- Array$create(Sys.time())
arr > "1980-01-01 00:00:00"
#> Error: NotImplemented: Function greater has no kernel matching input types (array[timestamp[us]], scalar[string])


# or a more real-world example
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
mtcars$date <- as.Date(c("1974-04-06", "1988-05-09"))

ds <- InMemoryDataset$create(mtcars)

ds %>%
  filter(date > "1980-01-01") %>%
  collect()
#> Error: NotImplemented: Function greater has no kernel matching input types (array[date32[day]], scalar[string])

Reporter: Jonathan Keane / @jonkeane
Assignee: Neal Richardson / @nealrichardson

Related issues:

• [R] Cast scalars to type of field in Expression building (is fixed by)

_{Note: This issue was originally created as ARROW-13938. Please see the migration documentation for further details.}

[asfimport]

Antoine Pitrou / @pitrou:
Interesting. For comparisons, that sounds reasonable, but I wonder if that may lead to undesirable behaviour for other kernels.

[asfimport]

Antoine Pitrou / @pitrou:
In general, I think we should be wary of non-trivial autocasts. Silently interpreting strings as other things (ints etc.) is a common source of bugs in language that have this, such as PHP and Javascript.

[asfimport]

Jonathan Keane / @jonkeane:
In general I agree that lots of string autocasts might be a source of more trouble than it's worth, Though dates/datetimes are special enough that we should consider autocasting them. R is not the only case where that works, many flavors of SQL do this, and pandas does too. Not that we should follow past decisions if we think they are wrong, but IMO doing this for dates/datetimes is beneficial.

>>> import pandas as pd
>>> df = pd.DataFrame({'date' : ['2020-08-09', '2020-08-25', '2020-09-05', 
...                              '2020-09-12', '2020-09-29', '2020-10-15', 
...                              '2020-11-21', '2020-12-02', '2020-12-10', 
...                              '2020-12-18']})
>>> 
>>> df['date'] = pd.to_datetime(df['date'])
>>> df["date"] > "2020-10-01"
0    False
1    False
2    False
3    False
4    False
5     True
6     True
7     True
8     True
9     True
Name: date, dtype: bool

[asfimport]

Antoine Pitrou / @pitrou:
@jorisvandenbossche What do you think?

[asfimport]

Rok Mihevc / @rok:
I've done this with SQL and Pandas in the past and it's a nice shorthand. Would be nice to have even in just in R/Python/.. wrapper.

 

Also semi-related - ARROW-13625 ([C++][CSV] Timestamp parsing should accept any valid ISO 8601 without requiring custom parse strings).

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
From my experience with pandas, fully agreed that autocasting strings as timestamps in operations is a very nice convenience.
And I think there are arguments for having timestamp be an exception here (as we indeed should not autocast to integers or floats), since you don't have a "native" scalar object like you have for ints, floats and strings, i.e. you need to create an object (eg calling datetime.datetime(), datetime.datetime.strptime() or pd.Timestamp() in Python). (although now writing this, the same could maybe be said for decimals ..)

I am only wondering a bit at what level this casting should be implemented. It might also be possible to let this be a responsibility of "user API" level (i.e. part of the R / Python bindings)? Or would that be difficult to implement at that level?

[asfimport]

Jonathan Keane / @jonkeane:
It's (probably) possible to implement these types of casts in the R/Python bindings, though doing so gets quite hairy. Specifically, it would require tracking (evaluated) types when constructing expressions. Though this type tracking in R is usually possible, but takes a lot of relatively error-prone book keeping.

[asfimport]

Antoine Pitrou / @pitrou:
Indeed, this is probably better done at the C++ level. cc @lidavidm

[asfimport]

Todd Farmer / @toddfarmer:
This issue was last updated over 90 days ago, which may be an indication it is no longer being actively worked. To better reflect the current state, the issue is being unassigned. Please feel free to re-take assignment of the issue if it is being actively worked, or if you plan to start that work soon.