Bug #100034 datetime precision incorrectly converted from MySQL datetime to Python datetime
Submitted: 29 Jun 2020 13:17 Modified: 30 Jun 2020 6:06
Reporter: server office Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:mysql-connector-python (8.0.20), 8.0.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: conversion, datetime, precision, python

[29 Jun 2020 13:17] server office
Description:
I found contribution #83479 from 2016 referering to same issue, but still it is not implemented/released yet.

I think this is an S2 (Serious) severity as it can modify important date precision e.g. in financial/trading scenarios which is critical to machine learning or trading decisions. Luckily we discovered the issue in testing.

Workaround is correcting each date after conversion, depending on precision, or using conversion to and from string, which adds overhead both on MySQL and Python.

How to repeat:
Steps to reproduce: sql date "2020-01-01 01:01:01.543" will be converted to Python as "2020-01-01 01:01:01.000543" in case of a datetime(3) column.

#############
#Create test Database:

CREATE DATABASE `bugTest`;
CREATE TABLE `bugTest`.`dates` ( `d1` datetime(3) DEFAULT NULL) ENGINE=InnoDB;
INSERT INTO `bugTest`.`dates` (`d1`) VALUES ("2020-01-01 01:01:01.543");

#############
# Python code:
# Tested with: mysql-connector-python (8.0.20)

import mysql.connector
from datetime import datetime, timedelta

dbCon = mysql.connector.connect(host="localhost", port=3306, user="testuser", passwd="*********", database="bugtest")
dbCur = dbCon.cursor(buffered=True, dictionary=True)

dbCur.execute("select d1 from bugtest.`dates`;")
row = dbCur.fetchone()
print ("datetime object from MySQL: {}".format(row["d1"]))

dbCur.execute("select CAST(`d1` AS char) as d1 from bugtest.`dates`;")
row = dbCur.fetchone()
print ("String   object from MySQL: {}".format(row["d1"]))

dbCur.close()
dbCon.close()

#############
# Printed results:

datetime object from MySQL: 2020-01-01 01:01:01.000543
String   object from MySQL: 2020-01-01 01:01:01.543
[30 Jun 2020 6:06] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh