Reading test file results in OverflowError: date value out of range

[cbrnr]

The haven package contains several .sav files for testing purposes:
https://github.com/tidyverse/haven/tree/master/tests/testthat

All files work except for datetime.sav. When I load this file with pyreadstat.read_sav("datetime.sav"), I get the following error:

OverflowError                             Traceback (most recent call last)
<ipython-input-32-f359011a603e> in <module>
----> 1 pyreadstat.read_sav("datetime.sav", dates_as_pandas_datetime=True)

pyreadstat/pyreadstat.pyx in pyreadstat.pyreadstat.read_sav()

pyreadstat/_readstat_parser.pyx in pyreadstat._readstat_parser.run_conversion()

pyreadstat/_readstat_parser.pyx in pyreadstat._readstat_parser.handle_value()

pyreadstat/_readstat_parser.pyx in pyreadstat._readstat_parser.convert_readstat_to_python_value()

pyreadstat/_readstat_parser.pyx in pyreadstat._readstat_parser.transform_datetime()

OverflowError: date value out of range

I also tried with dates_as_pandas_datetime=True, but I get the same error.

[ofajardo]

Reading it in R with haven, I realized that the thing is that this file contains a datetime (date.posix column) which has a crazy date: year 201,416!
(see screenshot). The thing is that python datetime supports only until year 9999 so that's why we get the overflow. Because most of us will be dead for year 201,416 (but I am sure that python will be alive and popular tough) and even for year 9999, and because I would like to keep using python datetime, I think this is a won't fix issue. Basically don't use this test file. BTW I also have a few in this repo under test data.

[cbrnr]

OK, thanks for checking. However, would it be possible to handle this situation more gracefully (e.g. by not converting to a datetime)? Currently, pyreadstat can't read the file at all, whereas haven can load it, so I assume it is not a readstat issue.

[ofajardo]

It is a python issue as I have explained before. Readstat gives you the information about the variables including their types. So for a datatime column we get the name and the type, which I store somewhere. Then you start getting information about the rows, one column at a time. If the column has datatime type, it is just a number I try to convert it to a python datetime. Now, let's say you have processed a few thousand rows, and later a funny value as this one we saw comes. You cannot convert it to a datetime while all the other values are already converted. What to do? I think only failing because returning a number while all the other values are datetimes is going to be very confusing for the user. The alternative would be to have a flag to tell pyreadstat not to attempt to convert dates, times and timestamps to python datetime like objects but just let them as numbers, this has to be decided beforehand before calling the function.
R can convert it probably because POSIXct or lt does not have the limitation about what is the max date it can handle. The limitation is in python datetime which cannot handle large dates.

[cbrnr]

I think having an option to disable conversion to datetime would be great!

[cbrnr]

Although I agree such a value for the year field is probably not what the user wanted. @hadley did you intentionally set the year to 201416? I just checked, and SPSS does not enforce 4-digit year numbers, therefore this is a valid .sav file. I wonder why converting this value to a date works in R though, because both as.Date and as.POSIXct give an error when I use a year > 9999.

[cbrnr]

Edit: as.POSIXct works with years > 9999 when I supply the Unix time with some origin, e.g.:

> as.POSIXct(14312412234234, origin="1970-01-01")
[1] "455512-04-16 11:03:54 CEST"

[ofajardo]

@cbrnr OK, I will implement an option to disable date/time/datetime conversion! (it will disable it for all columns, this also will give some extra speed).

Regarding those test files in R: do you know if they were produced in SPSS or were they produced in R? I think it is best to work with the ones produced in the original application (mine are).

[cbrnr]

@ofajardo np.datetime64 can handle years > 9999 (at least with a unit of seconds), e.g.

np.datetime64("201416-09-22T00:00:00", "s")

I was wondering if you could use NumPy for the conversion instead of the datetime module...

[cbrnr]

Re the test files, I have no idea how they were produced, but @hadley should know.

[cbrnr]

But bear in mind that files produced directly with SPSS can contain such strange dates - I just tried this myself.

[cbrnr]

Re np.datetime64, Pandas has a check which enforces 1677 < years < 2262. So even though the underlying data type does support a wider range, Pandas doesn't. I guess adding an option to disable date parsing is more or less the only option we have ATM.

[ofajardo]

Yes, I think disabling time comversion is the best atm.

This thing with datetimes is a little mess in pandas, I also remember that some methods (like pandas.to_datetime I think) enforce datetime64[ns] which have an even more limited range (because time is defined in nano-seconds). We sometimes use the year 9999 for certain things in our database for signaling something that is valid "for ever" and this pandas limitation already has bitten us a couple of times.

[ofajardo]

@cbrnr disable_datetime_conversion option ready on dev! I now can read this test file.

BTW I have added your name to the README on the collaborators section (on dev). Let me know if that is not fine with you or you suggest any change there.