timespan to timedelta dataframe conversion incorrect

Question

timespan to timedelta dataframe conversion incorrect

JPvRiel opened this issue 2 years ago · 1 comments

Code Sample, a copy-pastable example if possible

q = '''
let now = now();
let T = datatable(start:datetime, end:datetime) [
    datetime(2023-02-10T00:00:00.000000000+00:00), datetime(2023-02-11T00:00:00.327242700+00:00),
    datetime(2023-02-10T00:00:00.000000000+00:00), datetime(2023-02-11T00:00:00.000000000+00:00),
];
T
| extend delta = end - start
'''

%kql test_df << -auto_dataframe=True -query q
test_df

Problem description

When the timestamp has sub-second precision as per the above code example's first table row, it causes the conversion to a pandas data-frame to have an incorrect timedelta. E.g. it resulted in 38 days 21:00:27 instead of the correct detla of 1 days 0:00:00.32724700. When run in Log Analytics, the timespan value returned was 1.00:00:00.3272427. So it seems like there is some timespan -> timedelta conversion issue when sub-second precision occurs.

Interestingly, this problem does not manifest if the timespan did not inlcude sub-second precision values as per the second row in the example table.

If query related, does it happen on other platforms (Kusto Web UI, Kusto Explorer, Log Analytics in Azure Monitor)?

No, Log Analytics returns expected timespan values.

Output of `pip freeze` and `%kql --bugreport`

[bugreport must be executed immediatly after bug noticed]

{'kqlmagic': {'version': '0.1.114.post22'},
 'platform': {'system': 'Linux', 'release': '5.15.0-58-generic'},
 'packages': {'python-dateutil': '2.8.2',
  'traitlets': '5.4.0',
  'prettytable': '3.4.1',
  'requests': '2.27.1',
  'msal': '1.19.0',
  'azure-identity': '1.11.0',
  'pandas': '1.5.2',
  'ipython': '8.5.0',
  'ipykernel': '6.15.3',
  'pygments': '2.13.0',
  'pyperclip': '1.8.2',
  'azure-common': '1.1.28',
  'msrestazure': '0.6.4',
  'psutil': '5.9.2',
  'matplotlib': '3.5.3',
  'setuptools': '65.3.0',
  'plotly': '5.10.0',
  'flask': '2.2.2',
  'isodate': '0.6.1',
  'markdown': '3.4.1',
  'beautifulsoup4': '4.11.1',
  'lxml': '4.9.1',
  'ipywidgets': '7.7.2',
  'cryptography': '36.0.1',
  'password_strength': '0.0.3.post2'},
 'python': {'name': 'CPython', 'version': '3.10.9', 'branch': ''},
 'kqlmagic_default_options': {'add_kql_ref_to_help': True,
  'add_schema_to_help': True,
  'allow_py_comments_before_cell': True,
  'allow_single_line_cell': True,
  'assign_var': None,
  'auth_token_warnings': False,
  'auth_use_http_client': False,
  'auto_dataframe': False,
  'auto_limit': 1000000,
  'auto_popup_schema': True,
  'cache': None,
  'cache_folder_name': 'cache_files',
  'check_magic_version': True,
  'cloud': 'public',
  'code_auth_interactive_mode': 'device_code',
  'columns_to_local_vars': False,
  'cursor_var': None,
  'debug': False,
  'device_code_login_notification': 'button',
  'device_code_notification_email': '',
  'display_limit': 100,
  'dsn_filename': 'odbc.ini',
  'dynamic_to_dataframe': 'object',
  'enable_add_items_to_help': True,
  'enable_curly_brackets_params': False,
  'enable_sso': False,
  'enable_suppress_result': True,
  'export_folder_name': 'exported_files',
  'extras_require': 'default',
  'feedback': True,
  'is_kernel_intializtion': False,
  'is_magic': True,
  'json_display': 'raw',
  'kernel_id': '***',
  'kernel_location': 'local',
  'kqlmagic_kernel': False,
  'last_raw_result_var': '_kql_raw_result_',
  'notebook_app': 'visualstudiocode',
  'notebook_service_address': None,
  'palette_colors': 10,
  'palette_desaturation': 1.0,
  'palette_name': 'tab10',
  'plot_package': 'plotly',
  'plotly_config': None,
  'plotly_fs_includejs': False,
  'plotly_layout': None,
  'popup_interaction': 'auto',
  'prettytable_style': 'DEFAULT',
  'query_link_destination': 'Kusto.WebExplorer',
  'request_app_tag': None,
  'request_cache_max_age': 0,
  'request_id_tag': None,
  'request_user_agent_tag': None,
  'request_user_tag': None,
  'schema_json_display': 'raw',
  'short_errors': True,
  'show_conn_info': 'current',
  'show_init_banner': False,
  'show_query': False,
  'show_query_link': False,
  'show_query_time': True,
  'show_what_new': True,
  'sso_db_gc_interval': 168,
  'table_package': 'prettytable',
  'temp_files_server': 'auto',
  'temp_files_server_address': 'http://127.0.0.1:47111',
  'temp_folder_location': 'user_dir',
  'temp_folder_name': 'temp_files',
  'test_notebook_app': 'none',
  'timeout': 120,
  'try_azcli_login': False,
  'try_azcli_login_by_profile': False,
  'try_azcli_login_subscription': None,
  'try_kqlmaic_sso': False,
  'try_msi': None,
  'try_token': None,
  'try_vscode_login': False,
  'use_cache': None,
  'validate_connection_string': True,
  'warn_missing_dependencies': True,
  'warn_missing_env_variables': True},
'kqlmagic_default_env': {'KQLMAGIC_CONFIGURATION': '\'show_init_banner=False;device_code_login_notification="button";timeout=120;auto_limit=1000000;display_limit=100;json_display="raw";schema_json_display="raw"\''}
}

[paste the output of pip freeze here below this line]

absl-py==1.2.0
adal==1.2.7
ansible-compat==2.2.1
ansible-core==2.12.9
ansible-lint==6.8.2
anyio==3.6.1
argon2-cffi==21.3.0
argon2-cffi-bindings==21.2.0
asn1crypto==1.5.1
astroid==2.12.10
asttokens==2.0.8
astunparse==1.6.3
attrs==22.1.0
azure-common==1.1.28
azure-core==1.25.1
azure-identity==1.11.0
Babel==2.10.3
backcall==0.2.0
beautifulsoup4==4.11.1
black==22.10.0
bleach==5.0.1
blessed==1.19.1
bokeh==2.4.3
bpython==0.23
bracex==2.3.post1
cachetools==5.2.0
certifi==2022.9.14
certvalidator==0.11.1
cffi==1.15.1
chardet==5.0.0
charset-normalizer==2.0.12
click==8.1.3
cloudpickle==2.2.0
colorama==0.4.5
colorlover==0.3.0
commonmark==0.9.1
contourpy==1.0.5
cramjam==2.6.2
cryptography==36.0.1
cufflinks==0.17.3
curtsies==0.4.0
cwcwidth==0.1.7
cycler==0.11.0
dask==2022.9.1
debugpy==1.6.3
decorator==5.1.1
defusedxml==0.7.1
dill==0.3.5.1
distributed==2022.9.1
entrypoints==0.4
executing==1.0.0
fastjsonschema==2.16.2
fastparquet==2023.1.0
filelock==3.8.0
Flask==2.2.2
flatbuffers==2.0.7
fonttools==4.37.3
fsspec==2022.8.2
gast==0.4.0
google-auth==2.11.1
google-auth-oauthlib==0.4.6
google-pasta==0.2.0
greenlet==1.1.3
grpcio==1.49.1
h5py==3.7.0
HeapDict==1.0.1
htmlmin==0.1.12
humanize==4.4.0
humanreadable==0.1.0
idna==3.4
ImageHash==4.3.0
ipykernel==6.15.3
ipython==8.5.0
ipython-genutils==0.2.0
ipywidgets==7.7.2
isodate==0.6.1
isort==5.10.1
itsdangerous==2.1.2
jedi==0.18.1
Jinja2==3.1.2
jmespath==1.0.1
joblib==1.1.0
json5==0.9.10
jsonschema==4.16.0
jupyter==1.0.0
jupyter-client==7.3.4
jupyter-console==6.4.4
jupyter-core==4.11.1
jupyter-server==1.19.1
jupyterlab==3.4.8
jupyterlab-pygments==0.2.2
jupyterlab-widgets==1.1.1
jupyterlab_server==2.15.2
keras==2.10.0
Keras-Preprocessing==1.1.2
kiwisolver==1.4.4
Kqlmagic==0.1.114.post22
lazy-object-proxy==1.7.1
libclang==14.0.6
locket==1.0.0
lxml==4.9.1
Markdown==3.4.1
MarkupSafe==2.1.1
matplotlib==3.5.3
matplotlib-inline==0.1.6
mbstrdecoder==1.1.1
mccabe==0.7.0
missingno==0.5.1
mistune==2.0.4
modin==0.15.3
msal==1.19.0
msal-extensions==1.0.0
msgpack==1.0.4
msrest==0.7.1
msrestazure==0.6.4
multimethod==1.8
mypy-extensions==0.4.3
nbclassic==0.4.4
nbclient==0.6.8
nbconvert==7.0.0
nbformat==5.6.0
nest-asyncio==1.5.5
networkx==2.8.6
notebook==6.4.12
notebook-shim==0.1.0
numpy==1.23.3
oauthlib==3.2.1
opt-einsum==3.3.0
oscrypto==1.3.0
packaging==21.3
pandas==1.5.2
pandas-profiling==3.3.0
pandocfilters==1.5.0
parso==0.8.3
partd==1.3.0
passlib==1.7.4
password-strength==0.0.3.post2
pathspec==0.10.1
patsy==0.5.2
pexpect==4.8.0
phik==0.12.2
pickleshare==0.7.5
Pillow==9.2.0
platformdirs==2.5.2
plotly==5.10.0
portalocker==2.5.1
prettytable==3.4.1
prometheus-client==0.14.1
prompt-toolkit==3.0.31
protobuf==3.19.5
psutil==5.9.2
ptyprocess==0.7.0
pure-eval==0.2.2
pyarrow==10.0.1
pyasn1==0.4.8
pyasn1-modules==0.2.8
pycparser==2.21
pydantic==1.9.2
Pygments==2.13.0
PyJWT==2.5.0
pylint==2.15.3
PyNaCl==1.5.0
pyparsing==3.0.9
pyperclip==1.8.2
pyrsistent==0.18.1
python-dateutil==2.8.2
pytz==2022.2.1
PyWavelets==1.4.1
pyxdg==0.28
PyYAML==6.0
pyzmq==24.0.1
qtconsole==5.3.2
QtPy==2.2.0
requests==2.27.1
requests-oauthlib==1.3.1
resolvelib==0.5.4
rich==12.6.0
rsa==4.9
ruamel.yaml==0.17.20
ruamel.yaml.clib==0.2.6
scikit-learn==1.1.2
scipy==1.9.1
screeninfo==0.8.1
seaborn==0.11.2
Send2Trash==1.8.0
shtab==1.5.5
six==1.16.0
sklearn==0.0
sniffio==1.3.0
sortedcontainers==2.4.0
soupsieve==2.3.2.post1
stack-data==0.5.0
statsmodels==0.13.2
subprocess-tee==0.3.5
tangled-up-in-unicode==0.2.0
tblib==1.7.0
tenacity==8.1.0
tensorboard==2.10.0
tensorboard-data-server==0.6.1
tensorboard-plugin-wit==1.8.1
tensorflow==2.10.0
tensorflow-estimator==2.10.0
tensorflow-io-gcs-filesystem==0.27.0
termcolor==2.0.1
terminado==0.15.0
threadpoolctl==3.1.0
tinycss2==1.1.1
tldr==3.1.0
tomli==2.0.1
tomlkit==0.11.4
toolz==0.12.0
tornado==6.1
tqdm==4.64.1
traitlets==5.4.0
typepy==1.3.0
types-cryptography==3.3.23
typing_extensions==4.3.0
urllib3==1.26.12
vcert==0.15.0
visions==0.7.5
wcmatch==8.4.1
wcwidth==0.2.5
webencodings==0.5.1
websocket-client==1.4.1
Werkzeug==2.2.2
widgetsnbextension==3.6.1
wrapt==1.14.1
yamllint==1.28.0
zict==2.2.0

Answer 1 · 2023-03-09T15:11:38.000Z

Just bumping this with a screenshot as a demo of the issue. As a workaround, converting to a csv and then into a dataframe avoids this problem.

Code Sample, a copy-pastable example if possible

Problem description

If query related, does it happen on other platforms (Kusto Web UI, Kusto Explorer, Log Analytics in Azure Monitor)?

Output of pip freeze and %kql --bugreport

Output of `pip freeze` and `%kql --bugreport`