| Bug #100034 | datetime precision incorrectly converted from MySQL datetime to Python datetime | ||
|---|---|---|---|
| Submitted: | 29 Jun 2020 13:17 | Modified: | 11 May 2022 17:47 |
| Reporter: | server office | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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 | ||
[30 Jun 2020 6:06]
MySQL Verification Team
Hello! Thank you for the report and test case. regards, Umesh
[11 May 2022 17:47]
Philip Olson
It appears this bug was forgotten/lost over time, and we're sorry for the delayed response. Bug #83479 was fixed in v8.0.23. Adding this bug number to that release note. Thank you for being persistent with a follow-up bug report.
[5 May 2023 8:05]
MySQL Verification Team
Quoting from Bug #83479, Fixed in MySQL Connector/Python 8.0.23 release, and here's the proposed changelog entry from the documentation team: Fixed the microsecond conversion from MySQL datetime to Python datetime when using fractional values with the C extension enabled. For example, 2016-10-20 15:40:23.8 from MySQL resulted in datetime.datetime(2016, 10, 20, 15, 40, 23, 8) instead of datetime.datetime(2016, 10, 20, 15, 40, 23, 800000). Thanks to Vilnis Termanis for the patch.
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