[MCOL-1973] A `time` datatype is interpreted in Python as datetime.timedelta Created: 2018-11-28  Updated: 2018-11-28  Resolved: 2018-11-28

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: None
Fix Version/s: Icebox

Type: Bug Priority: Critical
Reporter: Martin Adamec Assignee: Andrew Hutchings (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Server version: 10.3.10-MariaDB-log Columnstore 1.2.1-1

Python 3.6.6 (default, Aug 13 2018, 18:24:23)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-28)] on linux

PyMySQL 0.9.2 (imported into python script as: `import pymysql`)


 Description   

Following is ColumnStore table structure containing time field of type datatype:
MariaDB [tradealert]> desc onelots;
----------------------------------------+

Field Type Null Key Default Extra

----------------------------------------+

trade_id int(11) YES   NULL  
secid int(11) YES   NULL  
side tinyint(4) YES   NULL  
size int(11) YES   NULL  
price float YES   NULL  
exch char(1) YES   NULL  
time time YES   NULL  
date date YES   NULL  
bexch char(1) YES   NULL  
aexch char(1) YES   NULL  
bid float YES   NULL  
ask float YES   NULL  
bsize smallint(6) YES   NULL  
asize smallint(6) YES   NULL  
spot float YES   NULL  
spot_chg float YES   NULL  
volume int(11) YES   NULL  
open_int int(11) YES   NULL  
cond char(1) YES   NULL  
root char(8) YES   NULL  
delta float YES   NULL  
ivol float YES   NULL  
ivol_chg float YES   NULL  
usymbol char(6) YES   NULL  
expiry date YES   NULL  
put_call char(1) YES   NULL  
strike float YES   NULL  
vega float YES   NULL  
theta float YES   NULL  
theo float YES   NULL  
nonstd tinyint(4) YES   NULL  

----------------------------------------+
31 rows in set (0.001 sec)

When running following query from Python the resultset returned is rendeting time field for each record returned as datetime.timedelta.

query = "SELECT sio.`id` AS option_id, o.`date` AS market_date, o.`time`, o.`size`, o.`price`, o.`spot`, "\
"o.open_int, o.theo, o.ivol, o.delta, o.vega, o.theta, o.`secid` "\
"FROM onelots AS o LEFT JOIN securities_occ AS so ON o.secid = so.secid "\
"LEFT JOIN dds_main.`sec_instrument_option` AS sio ON so.`occ_identifier` = sio.`occ_identifier` "\
"WHERE o.date = '2018-01-02' LIMIT 10"

I am not sure where is the problem but I would say that Python driver does not recognize time datatype and wrongly interprets it.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-11-28 ]

This appears to be an issue inside PyMySQL and is likely by design. The TIME datatype range is '-838:59:59' to '838:59:59' whereas Python's datetime.time can only hold '00:00:00' to '23:59:59'.

Either way this unfortunately is not a bug in MariaDB or ColumnStore.

Generated at Thu Feb 08 02:32:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.