I'm receiving an error when trying to query data fields in Denodo that have a timestamp type. I'm using python 3.7.3 and the jaydebeapi.
Referencing the error I receive below, '%Y-%m-%d %H:%M:%S' is indeed how the field is formatted, and '62690377-06-10 23:2' doesn't appear in the original data at all.
- If I convert the field to a string, the query works fine.
- Other users in my group are able to query the timestamp data without issues using the same version of Python and jaydebeapi and the code snippet below.
query_complete = (" Select \"DATE\" FROM \"ABC\".\"simple_table\" ")
cursor.execute(query_complete)
rows = cursor.fetchall()
complete_df = pd.DataFrame(rows, columns = list(zip(*cursor.description))[0]
ValueError Traceback (most recent call last)
<ipython-input-7-120011c28139> in <module>
21
22 cursor.execute(query_complete)
---> 23 rows = cursor.fetchall()
24 complete_df = pd.DataFrame(rows, columns = list(zip(*cursor.description))[0])
25
~\AppData\Local\Continuum\anaconda3\lib\site-packages\jaydebeapi\__init__.py in fetchall(self)
558 rows = []
559 while True:
--> 560 row = self.fetchone()
561 if row is None:
562 break
~\AppData\Local\Continuum\anaconda3\lib\site-packages\jaydebeapi\__init__.py in fetchone(self)
530 sqltype = self._meta.getColumnType(col)
531 converter = self._converters.get(sqltype, _unknownSqlTypeConverter)
--> 532 v = converter(self._rs, col)
533 row.append(v)
534 return tuple(row)
~\AppData\Local\Continuum\anaconda3\lib\site-packages\jaydebeapi\__init__.py in _to_datetime(rs, col)
582 if not java_val:
583 return
--> 584 d = datetime.datetime.strptime(str(java_val)[:19], "%Y-%m-%d %H:%M:%S")
585 d = d.replace(microsecond=int(str(java_val.getNanos())[:6]))
586 return str(d)
~\AppData\Local\Continuum\anaconda3\lib\_strptime.py in _strptime_datetime(cls, data_string, format)
575 """Return a class cls instance based on the input string and the
576 format string."""
--> 577 tt, fraction, gmtoff_fraction = _strptime(data_string, format)
578 tzname, gmtoff = tt[-2:]
579 args = tt[:6] + (fraction,)
~\AppData\Local\Continuum\anaconda3\lib\_strptime.py in _strptime(data_string, format)
357 if not found:
358 raise ValueError("time data %r does not match format %r" %
--> 359 (data_string, format))
360 if len(data_string) != found.end():
361 raise ValueError("unconverted data remains: %s" %
ValueError: time data '62690377-06-10 23:2' does not match format '%Y-%m-%d %H:%M:%S'`
I tried making a dummy data set to illustrate the issue. Sometimes as in this case, if the data set is small, I don't receive the value error but the output is garbled as can be seen when comparing the two sets of data below.
Query results when formatted as text in Denodo:
timestamp_text
0 2017-04-06 12:23:56
1 1998-06-01 23:23:26
2 1990-04-28 15:23:56
3 2007-12-06 16:13:37
4 2010-08-27 17:30:09
Query results for same data when formatted as timestamp in Denodo:
timestamp
0 1970-01-18 06:18:01.436000
1 1970-01-01 00:00:00
2 None
3 None
4 None
