[C++][CSV] Timestamp parsing should accept any valid ISO 8601 without requiring custom parse strings

[asfimport]

I was trying to read in some git logs and got this parse error for a column I had declared as timestamp type:

Error: Invalid: In CSV column #0: CSV conversion error to timestamp[s]: invalid value '2021-08-11T17:39:50-04:00'

This is valid ISO 8601 and is what git log produces with the I "strict ISO 8601 format" option (https://git-scm.com/docs/pretty-formats).

I see mentioned on ARROW-10343 that timezone indicators are not supported--is that still true? And I recognize that it's not trivial because a timestamp array has to have the same timezone for all values, so if some rows in this CSV had different timezones listed, we would have to handle that (converting everything to UTC is probably the most useful thing but technically loses information).

Reporter: Neal Richardson / @nealrichardson
Watchers: Rok Mihevc / @rok

Related issues:

• [C++] Unable to parse strings into timestamps (relates to)
• [C++] Strptime ignores timezone information (relates to)
• [C++] Can't cast from timestamp to utf8 when saving CSV (relates to)
• [C++] Allow timestamp parser to parse offset strings (relates to)

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

[asfimport]

Weston Pace / @westonpace:
I think converting everything to UTC is fine. If people really care about time zone information it should be stored out of band.

[asfimport]

Rok Mihevc / @rok:
https://issues.apache.org/jira/browse/ARROW-12820 and https://issues.apache.org/jira/browse/ARROW-13348 are also related.

[asfimport]

Nicola Crane / @thisisnic:
I believe this is resolved now; here's what I think is a reprex which I ran with a recent version of Arrow:

library(arrow)
#> 
#> Attaching package: 'arrow'
#> The following object is masked from 'package:utils':
#> 
#> timestamp
csv_file = tempfile()
writeLines("x\n2021-08-11T17:39:50-04:00", con = csv_file)
read_csv_arrow(csv_file)
#> # A tibble: 1 × 1
#> x 
#> <dttm> 
#> 1 2021-08-11 21:39:50

[asfimport]

Nicola Crane / @thisisnic:
That said, it goes wrong when using a schema:

library(arrow)
#> 
#> Attaching package: 'arrow'
#> The following object is masked from 'package:utils':
#> 
#> timestamp
csv_file = tempfile()
writeLines("x\n2021-08-11T16:39:50-04:00", con = csv_file)
read_csv_arrow(csv_file)
#> # A tibble: 1 × 1
#> x 
#> <dttm> 
#> 1 2021-08-11 20:39:50
read_csv_arrow(csv_file, skip = 1, schema=schema(x = timestamp()))
#> Error in `handle_csv_read_error()`:
#> ! Invalid: In CSV column #0: CSV conversion error to timestamp[s]: expected no zone offset in '2021-08-11T16:39:50-04:00'
#> /home/nic2/arrow/cpp/src/arrow/csv/converter.cc:550 decoder_.Decode(data, size, quoted, &value)
#> /home/nic2/arrow/cpp/src/arrow/csv/parser.h:123 status
#> /home/nic2/arrow/cpp/src/arrow/csv/converter.cc:554 parser.VisitColumn(col_index, visit)
#> ℹ If you have supplied a schema and your data contains a header row, you should supply the argument `skip = 1` to prevent the header being read in as data.
read_csv_arrow(csv_file, skip = 1, schema=schema(x = timestamp(timezone = "-4:00")))
#> # A tibble: 1 × 1
#> x 
#> <dttm> 
#> 1 2021-08-11 20:39:50 

[asfimport]

Nicola Crane / @thisisnic:
I think what is happening is that when we say that a column is a timestamp(), if we don't include a timezone in the call to timestamp(), then it's expected to be a a timestamp without a timezone, which is not true. I think ARROW-14442 is related to this. @dragosmg - will your changes on the PR for that ticket affect this one?

[asfimport]

Dragoș Moldovan-Grünfeld / @dragosmg:
ARROW-14442 looks at timestamps without timezone and assumes (just like R does) they are timestamps with the local timezone. As far as I can tell from a quick skim of this conversations the 2 issues are slightly different. I think the timestamp Neil is referring to contains the timezone as offset from UTC (the %z in R's format) which doesn't seem to be recognised by arrow (which recognises only string timezones - R's %Z).

A small reprex:

x <- as.POSIXct("2022-02-09", tz = "Europe/Bucharest")
format(x, format = "%z")
#> [1] "+0200"
format(x, format = "%Z")
#> [1] "EET"

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
I think this issue is indeed resolved. By default, Arrow will now infer a "timestamp with timezone UTC" type for such data:

import io
from pyarrow import csv

s = """a
2021-08-11T17:39:50-04:00"""

>>> csv.read_csv(io.BytesIO(s.encode()))
pyarrow.Table
a: timestamp[s, tz=UTC]
----
a: [[2021-08-11 21:39:50]]

which seems like a good default behaviour?

It indeed fails when specifying "timestamp" as the type for that column:

>>> csv.read_csv(io.BytesIO(s.encode()), convert_options=csv.ConvertOptions(column_types={'a': pa.timestamp('ns')}))
...
ArrowInvalid: In CSV column #0: CSV conversion error to timestamp[ns]: expected no zone offset in '2021-08-11T17:39:50-04:00'

But that is probably fine? (otherwise you have the ambiguity about using the UTC value vs the printed value as the tz-naive timestamp)
The error message is also clear about not expecting a timezone offset. We could maybe further improve the error message by saying something about "... for a timestamp without timezone" to stress that it is because the specified type has no timezone.
(the reverse case of expecting a zone offset if there is none also has an expanded message:

arrow/cpp/src/arrow/csv/converter.cc

Lines 382 to 391 in c0bae8d

	return Status::Invalid("CSV conversion error to ", type_->ToString(),
	": expected a zone offset in '",
	std::string(reinterpret_cast<const char*>(data), size),
	"'. If these timestamps are in local time, parse them as "
	"timestamps without timezone, then call assume_timezone.");
	} else {
	return Status::Invalid("CSV conversion error to ", type_->ToString(),
	": expected no zone offset in '",
	std::string(reinterpret_cast<const char*>(data), size),
	"'");

)

And it works fine if you specify a timestamp type with timezone for the column:

>>> csv.read_csv(io.BytesIO(s.encode()), convert_options=csv.ConvertOptions(column_types={'a': pa.timestamp('ns', tz="UTC")}))
pyarrow.Table
a: timestamp[ns, tz=UTC]
----
a: [[2021-08-11 21:39:50.000000000]]

I don't think it is directly related to ARROW-14442 because here it is about parsing a string, while ARROW-14442 is about converting an R object that already represents some form of timestamp.

I think the timestamp Neil is referring to contains the timezone as offset from UTC (the %z in R's format) which doesn't seem to be recognised by arrow (which recognises only string timezones - R's %Z).

Arrow actually does "support" such offset timezone (%z), at least in the format specification (see

arrow/format/Schema.fbs

Lines 341 to 351 in c0bae8d

	/// The timezone is an optional string indicating the name of a timezone,
	/// one of:
	///
	/// * As used in the Olson timezone database (the "tz database" or
	/// "tzdata"), such as "America/New_York".
	/// * An absolute timezone offset of the form "+XX:XX" or "-XX:XX",
	/// such as "+07:30".
	///
	/// Whether a timezone string is present indicates different semantics about
	/// the data (see above).
	timezone: string;

). However, we currently don't really have support for that kind of timezone in any kernel.

So specifying such a offset timezone works:

>>> table = csv.read_csv(io.BytesIO(s.encode()), convert_options=csv.ConvertOptions(column_types={'a': pa.timestamp('ns', tz="+05:00")}))
>>> table 
pyarrow.Table
a: timestamp[ns, tz=+05:00]
----
a: [[2021-08-11 21:39:50.000000000]]

(now, this is a bit confusing because it doesn't actually matter here, I could also have used tz="blabla" and it would also have worked. It doesn't matter because once it a timestamp with timezone type, Arrow will just convert the string to the underlying UTC value, which is always the same regardless of the actual timezone).

But once you do an operation, it currently fails:

>>> import pyarrow.compute as pc
>>> pc.hour(table['a'])
...
ArrowInvalid: Cannot locate timezone '+05:00': +05:00 not found in timezone database

The JIRA for this is ARROW-14477

[asfimport]

Neal Richardson / @nealrichardson:
Confirming this is now fixed.