Dates less than epoch

[jimmykirk]

I've noticed this fails on historical data. I know mapd can handle back 1901-2038, but does anyone know if that's possible with this module?

[TomAugspurger]

Hi @jimmykirk that should be possible. Does your use-case fail with an exception, or silently return the wrong result?

In [5]: con.execute("create table date_test (date_ DATE);")
In [5]: con.execute("create table date_test (date_ DATE);");
In [7]: con.execute("insert into date_test VALUES ('1901-01-01');");
In [9]: list(con.execute("select * from date_test"))
Out[9]: [(-2177452800,)]

I'll put together a fix for that.

[jimmykirk]

If fails due to the negative number returned.

Traceback (most recent call last):
File "C:/Users/Jimmy/PycharmProjects/api_test/sandbox.py", line 18, in
for row in c.fetchall():
File "C:\Users\Jimmy\api_test\lib\site-packages\pymapd\cursor.py", line 170, in fetchall
return list(self)
File "C:\Users\Jimmy\api_test\lib\site-packages\pymapd\cursor.py", line 204, in make_row_results_set
data.row_set.columns)]
File "C:\Users\Jimmy\api_test\lib\site-packages\pymapd_parsers.py", line 50, in _extract_col_vals
vals = [datetime.datetime.utcfromtimestamp(v) for v in vals]
ValueError: timestamp out of range for platform localtime()/gmtime() function

This is due to the datetime.datetime.utcfromtimestamp requiring an epoch date after 1970. Is MapD returning the epoch time here? Once you go pretty far back, you start getting alpha chars in the string returned. I played with doing something to the effect of:

vals = [datetime.datetime.utcfromtimestamp(datetime.datetime(1970, 1, 1) + datetime.timedelta(
                    seconds=(v * 1000)))]

However, it seems like staying away from Epoch time in general would be kinder to historical data. I know mapd returns them in string form via their SQL Editor so I would hope we could pull that in and use strptime(). I haven't dug very deep as I am just getting started with mapd.

[TomAugspurger]

OK, the utcfromtimestamp behavior is platform specific apparently.

#49 should hopefully fix it.

[TomAugspurger]

@jimmykirk if you're able to install pymapd from master, this should be fixed for you. I'll be doing another release once #9 is done (hopefully in a day or two).

[jimmykirk]

@TomAugspurger

C:\Users\Jimmy\api_test\Scripts\python.exe C:/Users/Jimmy/PycharmProjects/api_test/sandbox.py
Traceback (most recent call last):
File "C:/Users/Jimmy/PycharmProjects/api_test/sandbox.py", line 18, in
for row in c.fetchall():
File "C:\Users\Jimmy\apit_test\lib\site-packages\pymapd\cursor.py", line 170, in fetchall
return list(self)
File "C:\Users\Jimmy\apit_test\lib\site-packages\pymapd\cursor.py", line 204, in make_row_results_set
data.row_set.columns)]
File "C:\Users\Jimmy\apit_test\lib\site-packages\pymapd_parsers.py", line 60, in _extract_col_vals
vals = [base + datetime.timedelta(seconds=v) for v in vals]
OverflowError: Python int too large to convert to C long

Process finished with exit code 1

The values in vals are:
<type 'list'>: [-5333126400L, 1483660800]

So the first is a long and not an int and I'm testing this on a Windows workstation, but I also gave it a shot on a linux machine as well and got the same thing. I tried both because I had read that python on Windows and Linux treat longs differently... I also saw recommendations to use numpy instead for consistency across platforms.

[TomAugspurger]

Thanks, I'll see if I can reproduce on a non-Mac machine. I assume you're using python2? In py3 the distinction between regular and long ints is gone.

[jimmykirk]

That is correct.
ver 2.7.14.

[randyzwitch]

If I'm reading this issue correctly, this should be fixed (I verified it's not an issue in Python 3.5 with Linux, don't have access to Windows and Python 2.7). Closing, but if this is still an issue, please let me know and we can re-open.