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
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