#325 Datetime fields with microsecond shows as None

[Dmitry T.]

When SELECT datatime field which contained microseconds from DB(MariaDB 5.5 and MySQL 5.6). Result can not be converted to datetime.datetime and data in that field shows as None. I use MariaDB 5.5.27.

For example:
Make table in DB and fill its

import MySQLdb
db=MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
c=db.cursor()
c.execure("""DROP TABLE IF EXISTS `t1`""")
c.execure("""CREATE TABLE `t1` (
  `id` int(11)
  `date` datatime(6) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8""")
c.execure("""INSERT INTO t1 VALUES(1,now(6)), VALUES(2,NOW(6)), VALUES(3,NOW(6))""")

Now we can get data.

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, None), (2L, None), (3L, None))

After patch:

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (2L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (3L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)))

[Andy Dustman]

It's not obvious what version of MySQLdb you tested against. Can you test against 1.2.4b2? I believe the issue is fixed there.

• assigned_to: Andy Dustman

[Dmitry T.]

I have tried in 1.2.4b4, result tuple have None if DateTime field contained microsecond.

Bug in DateTime_or_None() on times.py when func try to return value it can't split microsecond from time

return datetime(*[ int(x) for x in d.split('-')+t.split(':') ])

import datetime.datetime
d = '2013-03-28'
t = '11:11:11.000011'
datetime(*[ int(x) for x in d.split('-')+t.split(':') ])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: invalid literal for int() with base 10: '11.000011'

[Zmicier Zaleznicenka]

The example works for the new versions of MySQLdb, but DateTime_Or_None() still parses datetimes with microseconds incorrectly, which leads to problems for clients using it directly (see i.e. https://code.djangoproject.com/ticket/19716). If it is possible, please make changes to DateTime_Or_None().

[Zmicier Zaleznicenka]

Also, if talking about these ms issues, while Time_Or_None() parses microseconds, rounding errors may appear. Instead, I suggest to use string methods.

This happens now:

~~~~~

s='50.991'
s = float(s)
int(s)
50
int(math.modf(s)[0]*1000000)
990999

This is what I think it should be:

s='50.991'
s,ms = s.split('.')
int(s)
50
int(ms.ljust(6,'0'))
991000
~~~~~~

[Dmitry T.]

Yep, and this problem exist.

May be I can provide more simply way and without math module.

>>> s = '50.991'
>>> s = float(s)
>>> int(s)
50
>>> int(round(s - int(s),6) * 1000000)
991000
>>> 

[Zmicier Zaleznicenka]

Math module is used now and causes rounding errors. In my post I suggest using string method ljust instead of math.modf. Ljust is simpler and faster (i believe) than rounding the way you propose.

[Alexey Malashkevich]

Agree, there is no need in float here. Below is one of the ways to fix the problem:

def DateTime_or_None(s):
    if not ' ' in s and 'T' in s:
        return Date_or_None(s)

    try:
        if len(s) == 23: s = s + '000'
        s = s.replace('-', ' ').replace(':', ' ').replace('.', ' ').replace('T', ' ')
        return datetime(*[ int(x) for x in s.split()])
    except (SystemExit, KeyboardInterrupt):
        raise
    except:
        return Date_or_None(s)

[Dmitry T.]

New patch for solve this problem. Without float numbers.

[Mark Wilby]

I just ran into this problem while using twisted.enterprise.adbapi. A simple query of datetime objects returned a None object an all records. The patch here did not fix the problem as it was in the DateTime_or_None method which is not modified.

I guess its already fixed in the next version, but I have posted this just in case. Again its a problem of handling the milliseconds. I made a slight change to this method, adding an if statement after the split and it seems to work.

def DateTime_or_None(s):
    if ' ' in s:
        sep = ' '
    elif 'T' in s:
        sep = 'T'
    else:
        return Date_or_None(s)

    try:
        d, t = s.split(sep, 1)
        if '.' in t:
            t = t.replace('.', ':')
        else:
            t = t + ':0'
        return datetime(*[ int(x) for x in d.split('-')+t.split(':') ])
    except (SystemExit, KeyboardInterrupt):
        raise
    except:
        return Date_or_None(s)

I think the string replace is not too costly and everything else is the same.

I apologise, I have limited expertise with Python and its tools, so due to my ignorance I am just posting the code change, not the patch.

[Dmitry T.]

Please can you provide dataset sample for testing.
Try get sources of MySQLdb from https://github.com/farcepest/MySQLdb1
I have pushed changes to github repo.

[Mark Wilby]

Sorry for not replying yesterday, but your email made me notice
something strange and I needed to think about it.

At first glance the basic test outlined below should have picked up the
problem I saw. Essentially I was doing the same, retrieving results with
a datetime object in them and printing them out. I saw the same problem
both before and after the patch. This leaves two possibilities I can see.

1. I was using your code via twisted, which uses reflection to build a
   wrapper around your library. It might be possible that the two
   approaches, literally build a different stack sequence for
   processing an execute and different functions are used to convert
   the data to a datetime object. (something I find unlikely.)
2. The patch I applied was not the right one. Although I took it
   directly from the link on the bug report and it modified the methods
   TimeDelta_or_None and Time_or_None, which were never called in my
   test and it left the method DateTime_or_None unchanged. In the
   spirit of simplicity I suspect this is the explanation.

I have also noticed that due to these changes, the module is a now a bit
of a patchwork. For example format_TIMEDELTA does not support milliseconds.

I am bogged down with deadlines at the moment, but in the next few days
I will build a test example that I can release using the twisted
library. I have not had time to look at the development branch, but if
you don't already have it I could add a test case for this module. Its
not going to track the stack sequence on the call, but it should help to
keep track of the validity of the methods.

On 05/02/14 13:04, Dmitry T. wrote:

Please can you provide dataset sample for testing.
Try get sources of MySQLdb from https://github.com/farcepest/MySQLdb1
I have pushed changes to github repo.

---

[bugs:#325] http://sourceforge.net/p/mysql-python/bugs/325/
Datetime fields with microsecond shows as None

Status: open
Created: Wed Oct 03, 2012 03:21 PM UTC by Dmitry T.
Last Updated: Wed Feb 05, 2014 12:50 AM UTC
Owner: Andy Dustman

When SELECT datatime field which contained microseconds from
DB(MariaDB 5.5 and MySQL 5.6). Result can not be converted to
|datetime.datetime| and data in that field shows as |None|. I use
MariaDB 5.5.27.

For example:
Make table in DB and fill its

import MySQLdb
db=MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
c=db.cursor()
c.execure("""DROP TABLE IF EXISTS t1""")
c.execure("""CREATE TABLE t1 (
id int(11)
date datatime(6) NOT NULL,
PRIMARY KEY (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8""")
c.execure("""INSERT INTO t1 VALUES(1,now(6)), VALUES(2,NOW(6)), VALUES(3,NOW(6))""")

Now we can get data.

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, None), (2L, None), (3L, None))

After patch:

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (2L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (3L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)))

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/mysql-python/bugs/325/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[Dmitry T.]

Hi,

I'm sorry, this my fail. I was inattentive when read your comment
Please try replace functions code in times.py. Working code below.

def DateTime_or_None(s):
    if ' ' in s:
        sep = ' '
    elif 'T' in s:
        sep = 'T'
    else:
        return Date_or_None(s)

    try:
        d, t = s.split(sep, 1)
        if '.' in t:
            t, ms = t.split('.',1)
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        return datetime(*[ int(x) for x in d.split('-')+t.split(':')+[ms] ])
    except (SystemExit, KeyboardInterrupt):
        raise
    except:
        return Date_or_None(s)

def TimeDelta_or_None(s):
    try:
        h, m, s = s.split(':')
        if '.' in s:
            s, ms = s.split('.')
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        h, m, s, ms = int(h), int(m), int(s), int(ms)
        td = timedelta(hours=abs(h), minutes=m, seconds=s,
                       microseconds=ms)
        if h < 0:
            return -td
        else:
            return td
    except ValueError:
        # unpacking or int/float conversion failed
        return None

[Mark Wilby]

This works fine and the ljust protected against badly formatted time
strings, which my suggestion didn't do. I will use this version form now
on. Thanks for your time.

Just one last point, your attached code does not include the
Time_or_None method, which also needs to be changed. In the unlikely
event someone else is effected before the next release, they need to be
aware they need to change all three methods.

Just for completeness, using the same pattern I think that the
Time_or_None method would be

def Time_or_None(s):
    try:
        h, m, s = s.split(':')
        if '.' in s:
            s, ms = s.split('.')
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        h, m, s, ms = int(h), int(m), int(s), int(ms)
        return time(hour=h, minute=m, second=s, microsecond=ms)
    except ValueError:
        return None