JSON native support for datetime encoding
State: closed

This adds support at the C level for encoding datetime values in JSON. I added a new parameter `date_unit` which controls the precision of the encoding, options are seconds, milliseconds, microseconds and nanoseconds. It defaults to milliseconds which should fix #4362.

I also added support for NaTs and tried to detect when numpy arrays of datetime64 longs are passed. 

Note that datetime decoding is still handled at the Python level but I added a date_unit param here too so timestamps can be converted correctly.

valgrind looks happy with the changes and all tests pass ok for me on Python 2.7 & 3.3


Labels:

Comments:
Build failing on Python 2.6 and 3.2, I'll try to get my hands on 2.6 and take a look.

- release notes
- can you update examplse in io.rst for using date_unit (or I can do ...up 2 u)
- does the user need to specify the date_unit on deserialization as well? 

Ok
- [x] numpy 1.6 fixes
- [x] release notes
- [x] doc examples for date_unit

> does the user need to specify the date_unit on deserialization as well?

Yes date parsing won't work properly otherwise, added an example to the docs about this.

Rebased it all to current master. Want me to squash any of these commits?

look good...

I guess its 'impossible' to figure out if the user specified an 'invalid' date_unit, though if its out of range of valid dates then you could...?

e.g. if I wote a date in ns most read-back in s should fail

I guess we could try and sniff the values to try and deduce the relative magnitude, or alternatively do something like

``` python
for unit in ('s', 'ms', 'us', 'ns'):
    try:
        to_datetime(date_data, unit=unit)
        break
    except OverflowError:
        pass
```

which would only happen if the user passed `date_unit=None`....

ok....that makes sense....default will be 'ms' anyhow, correct?

Default will be `ms` when serialising but with the above change I'd be inclined to make `None` the default when deserialising?

Ok I've added timestamp precision detection when deserialising. It seems to work pretty well so I've set the default to `date_unit=None`. Let me know if you guys think a different default would be more appropriate.

@Komnomnomnom just a couple of minor points....this is pretty awesome though!

squash down to a couple of commits and do those minor points and can merge

Ok added a commit with the fixes, will wait to hear back before squashing, so it's easier to review the latest changes.

ok...one more thing....can you include a small json file from 0.12 (generate it with 0.12), and put in a compat checker, just to make sure that you would interpret correctly? (I think it works, just making sure we don't break the existing)...

Good call @jreback! This exposed a few things:

**1)** The NaT handling in 0.12 was not correct, this has already been addressed by the previous changes but it means I wasn't able to include NaT's in my compat test:

**0.12**

``` python
In [81]: s = pd.Series([pd.NaT])

In [82]: s.to_json()
Out[82]: '{"0":-9223372036854775808}'
```

**This PR**

``` python
In [7]: s = pd.Series([pd.NaT])

In [8]: s.to_json()
Out[8]: '{"0":null}'

```

Added a line to the release notes about this (bug fix).

**2)** This is an odd one, I don't fully understand why but if a date column including `NaT`s was serialised  to timestamps then there appears to be slightly different handling in `to_datetime` during deserialisation and the expected `OverflowError` is suppressed. I fixed this by adding `except -1` to allow the exception to be raised from the guilty Cython func.

``` cython
cdef inline int64_t cast_from_unit(object unit, object ts) except -1:
```

All pandas tests still pass for me with this change but I am not that familiar with Cython or this code though so....

**3)** Had to modify json.py a bit more to handle deserialising `NaT`s in date columns properly (they'll be deserialised to `NaN` by the native code). Changed check for `iNaT` to a call to `isnull`.

**4)** I removed the casting to `int64` when attempting the `to_datetime` call, it doesn't seem to be necessary?

I think in the call for NaT testnig, you still need to check if any `values == iNaT` because that value is only considered a null if the series is of dtype `M` (e.g. a datetime) at that time, in this case its an `int64` (the isnull will use the view to check, but I don't recall if the dtype is set at this point in the code)

Sweet, that makes it compatible with the NaT handling from 0.12. It still won't be able to parse NaT's from 0.12 serialised with iso format though:

``` python
In [187]: s = pd.Series([pd.NaT])

In [189]: s.to_json(date_format='iso')
Out[189]: '{"0":"0001-255-255T00:00:00"}'
```

gr8; since default was `epoch` anyhow, I think compat from 0.12 is pretty good 

looks good to go....pls squash down (after passing) and will get it in

Any idea why Travis would be choking on the compat test, it appears it might be a problem related to reading the external file, do they need to be whitelisted or anything?

Works for me locally on the same Python / numpy version.

In `setup.py` add `tests/data/*.json` in the following list (I don't think the data files are being copied to the test dirs)

```
      package_data={'pandas.io': ['tests/data/legacy_hdf/*.h5',
                                  'tests/data/legacy_pickle/0.10.1/*.pickle',
                                  'tests/data/legacy_pickle/0.11.0/*.pickle',
                                  'tests/data/*.csv',
                                  'tests/data/*.dta',
                                  'tests/data/*.txt',
                                  'tests/data/*.xls',
                                  'tests/data/*.xlsx',
                                  'tests/data/*.table',
                                  'tests/data/*.html'],
                    'pandas.tools': ['tests/*.csv'],
                    'pandas.tests': ['data/*.pickle',
                                     'data/*.csv'],
                    'pandas.tseries.tests': ['data/*.pickle',
                                             'data/*.csv']
                    },
```

Doh didn't think of that, thanks

Squishified commits and rebased to master. Good to go once travis signs off if you're happy with it

looks good to me! thanks! bombs away....

https://travis-ci.org/pydata/pandas/jobs/10223886

I had this fail once and then work locally....??