dropbox / dropbox/PyHive

Querying of date/datetime with year 1-999 is incorrect on some platforms

Open
#404 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
1.7k
Forks
545
PR merge metrics
No merged PRs in 30d

Description

Pyhive sqlachemy dialect can not do 'where' filtering for dates/timestamps with year lower than 1000 because leading zeros are omitted when %Y is used.
Years 1-999 are rare is real use, but may be used for relative timestamps for example, or when only time part is used.

This problem may appear on some systems depending of strftime implementation of system libraries. See https://bugs.python.org/issue13305

problematic method: pyhive.common.ParamEscaper.escape_datetime

This is a issue with python itself, but a workaround can be implemented for this case.
One solution is to use %04Y to set fixed with of 4, but it is also platform dependent.
Another is to use hack:
```
dt_str = '{:0>4}{}'.format(
item.strftime('%Y'),
item.strftime(format_.lstrip('%Y'))
)
```
This should be portable.

**Reproduce**
```
CREATE TABLE DateTimeTable(
date DATE,
timestamp TIMESTAMP
);
insert into DateTimeTable values ('0001-01-01', '0001-01-01T00:00:00.000000Z');
```

```
from datetime import datetime
from sqlalchemy import MetaData

...
meta = MetaData()
meta.reflect(bind=dbfs_engine)

ts = datetime.strptime('0001-01-01T00:00:00.000000', "%Y-%m-%dT%H:%M:%S.%f")
print(ts.strftime('%04Y-%m-%d %H:%M:%S.%f'))
print(ts.strftime('%Y-%m-%d %H:%M:%S.%f'))

table = meta.tables['datetimetable']
q = table.select(table.c['timestamp'] == ts)
q.bind = dbfs_engine
res = dbfs_engine.execute(q)

for r in res:
print(r)
# Nothing here
# Expected 1 row
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.