[R] default TZ parsing woes in CSV reader

[asfimport]

I am attempting to use open_dataset() on a large collection of CSV files in which a timestamp column sometimes has a date format and sometimes a timezone format.

readr is fine reading these both in with a col_type set to "timestamp" (i.e. see below), but arrow_read_csv insists the one must use tz="UTC" while the other must not use tz="UTC" in order for the schema to be valid.  Easiest to see this in a simple example:

x <- tempfile()
df <- data.frame(time = '2021-02-01T00:00:00Z')
readr::write_csv(df, x)
schema = arrow::schema(time = timestamp("s", ""))

# ERROR cannot parse w/o tz="UTC" in the schema:
arrow::read_csv_arrow(x,schema = schema, skip=1) 

df2 <- readr::read_csv(x, col_types="T")  # works fine

df <- data.frame(time = '2021-02-01')
readr::write_csv(df, x)
## ERROR cannot parse w/ tz="UTC" :
schema = arrow::schema(time = timestamp("s", "UTC")) 
arrow::read_csv_arrow(x,schema = schema, skip=1)

## Once again, readr has no issues:
df2 <- readr::read_csv(x, col_types="T")
 

Reporter: Carl Boettiger / @cboettig
Watchers: Rok Mihevc / @rok

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

[asfimport]

Weston Pace / @westonpace:
Are "2021-02-01T00:00:00Z" and "2021-02-01" the same instant? (readr seems to consider these the same instant in time)

Would "2021-02-01T00:00:00-07:00" and "2021-02-01" be the same instant? (readr does not seem to consider these the same instant in time)

In other words should values without a datetime be blindly assumed to be UTC? Or should a new CSV reader argument be added (the time zone to be used when one isn't present).

[asfimport]

Carl Boettiger / @cboettig:
Thanks, I understand the potential ambiguities here, but merely
looking ro consistent behavior.

In the first instance, the data shows a full timestamp with a Z suffix
indicating the UTC timezone. I understand why in the case of an
explicit timezone it would be acceptable to omit the timezone,
timestamp("s", "") in the schema, but I feel that it is a bug that I
get an error when I manually declare the timezone as UTC explicitly,
as shown in the first example. Surely I should be allowed to
precisely declare the timezone being used? If I was allowed to use
UTC in the first example, then this would also entirely resolve the
issue.

I do see that the second case is less clear-cut – if we declare a
date with no TZ information in the string must be read as a timestamp,
this possibly is ambiguous. However, at least in R, the convention of
tz="" is defined in as.POSIXct to adopt the local timezone (i.e. as
configured by the operating system), rather than throw an error. So
the behavior of readr is definitely well-defined and expected from the
perspective of the R user at least, and arguably somewhat sensible at
least as a default.

So resolving the first error is definitely the more important to me –
I feel I should be able to explicitly declare the tz and not be forced
to use tz="" in my schema when the data has unambiguous timestamp
formats. But from a user perspective I think the second case would be
more streamlined if it defaulted to the system setting rather than
refusing the coercion, but I'm fine if you want to ignore that case.

Does that make sense?

Apologies I was too brief in the original report (had to get off my train!)

• Carl

[asfimport]

Jonathan Keane / @jonkeane:
This is tangentially related, but in relation to the second case, it would be good to get feedback on: https://issues.apache.org/jira/browse/ARROW-14442

[asfimport]

Carl Boettiger / @cboettig:
Just a correction – this only impacts the development version, 6.0.2, not 6.0.1 (which is more annoying in a way since it means the dev version also breaks existing workflows.)

This may be related to https://issues.apache.org/jira/browse/ARROW-15105 , which is fixed in the dev version but not in the current release.

[thisisnic]

This issue has been closed due to having no activity since 2021 or earlier. Feel free to reopen if this has been closed in error.