TableTools.readCsv should be able to read the timestamp string representation Pandas uses (seems to be RFC3339).

[jcferretti]

A file generated in python pandas using .to_csv(...) for a dataframe that was loaded from a parquet file with a timestamp(nanos) type column:

cfs@erke 16:55:20 ~/dh/oss1/deephaven-core/data
$ head -n 10 /a1/tmp/workqueue-no-nulls-100m-no-index.csv
workqueue_id,user_id,timestamp
1,1001,2021-11-07 14:00:00.011463807+00:00
2,1003,2021-11-07 14:00:00.080748962+00:00
3,1002,2021-11-07 14:00:00.114976256+00:00
4,1004,2021-11-07 14:00:00.118275772+00:00
5,1003,2021-11-07 14:00:00.131173174+00:00
6,1001,2021-11-07 14:00:00.174901899+00:00
7,1001,2021-11-07 14:00:00.194499326+00:00
8,1002,2021-11-07 14:00:00.201754097+00:00
9,1004,2021-11-07 14:00:00.237870788+00:00

The timestamp column formatted as above is not recognized by DHC when using TableTools.readCsv; the column is read as String.

More generally, we should accept valid ISO 8601 strings as timestamps, any of them.
As Ryan points out below, this is not valid ISO 8601; it seems to be valid RFC3339 but is not necessarily what Pandas is even trying to do here; read the sequence of comments that follows.

[rcaudy]

@devinrsmith did add support for ISO 8601 timestamps. I think we're looking for 'T' as a separator. Wikipedia claims space is not an acceptable separator:
https://en.wikipedia.org/wiki/ISO_8601

A single point in time can be represented by concatenating a complete date expression, the letter "T" as a delimiter, and a valid time expression. For example, "2007-04-05T14:30". In ISO 8601:2004 it was permitted to omit the "T" character by mutual agreement as in "200704051430",[33] but this provision was removed in ISO 8601-1:2019. Separating date and time parts with other characters such as space is not allowed in ISO 8601, but allowed in its profile RFC 3339.[34]

[rcaudy]

We should consider whether to be more permissive than the ISO standard.

[jcferretti]

We should consider whether to be able to load csv files that pandas produces by default?

[rcaudy]

Right now, the code is effectively using:

java.time.format.DateTimeFormatter#ISO_INSTANT

This looks like it's quite authoritatively parsing strict ISO 8601. We might be able to use java.time.format.DateTimeFormatterBuilder to build something more lenient.

@jcferretti Does Pandas publish a discussion for what kind of permissive formats they allow outside of the ISO standard?

[jcferretti]

This seems relevant:

https://stackoverflow.com/questions/522251/whats-the-difference-between-iso-8601-and-rfc-3339-date-formats:

The RFC also allows the "T" to be replaced by a space

[jcferretti]

More context:

https://stackoverflow.com/questions/65000211/is-using-t-to-separate-date-and-time-inconsistent-with-rfc3339

This mentions Postgres does the same on output (space).

[rcaudy]

So, maybe we turn this discussion into a feature request to support RFC-3339 timestamps. What I don't want is an open-ended "support whatever Pandas does" ticket without more research.

[jcferretti]

After reading a bit more, my guess is, pandas dataframe.to_csv() (without further date format specification, ie, by default) is doing what python does for converting its datetime type to string.

This is a guess, it should be confirmed.

https://docs.python.org/3/library/datetime.html

datetime.__str__()¶

    For a datetime instance d, str(d) is equivalent to d.isoformat(' ').

[jcferretti]

This may shed a bit more light:

cfs@erke 17:24:13 ~/dh/oss1/deephaven-core/data
$ /l/parquet-cli/1.12.1/bin/parquet-cli meta ~/dh/oss1/deephaven-core/data/workqueue-no-nulls-100m.parquet  | head -n 10

File path:  /a0/h/cfs/dh/oss1/deephaven-core/data/workqueue-no-nulls-100m.parquet
Created by: parquet-mr version 1.12.0 (build db75a6815f2ba1d1ee89d1a90aeb296f1f3a8f20)
Properties: (none)
Schema:
message MyMessage {
  optional int32 workqueue_id;
  optional int32 user_id;
  optional int64 timestamp (TIMESTAMP(NANOS,true));
}
(pyarrow) 
cfs@erke 17:24:26 ~/dh/oss1/deephaven-core/data
$ python3 
Python 3.8.10 (default, Sep 28 2021, 16:10:42) 
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyarrow.parquet as pq
>>> t = pq.read_table('/a0/h/cfs/dh/oss1/deephaven-core/data/workqueue-no-nulls-100m.parquet')
>>> print(t.schema)
workqueue_id: int32
user_id: int32
timestamp: timestamp[ns, tz=UTC]
>>> df = t.to_pandas()
>>> print(df.dtypes)
workqueue_id                  int32
user_id                       int32
timestamp       datetime64[ns, UTC]
dtype: object

[devinrsmith]

Happy to support the common date-time formats that python/pandas produces by default.