25

A mysql database table has a column whose datatype is time ( http://dev.mysql.com/doc/refman/5.0/en/time.html ). When the table data is accessed, Python returns the value of this column as a datetime.timedelta object. How do I extract the time out of this? (I didn't really understand what timedelta is for from the python manuals).

E.g. The column in the table contains the value "18:00:00" Python-MySQLdb returns this as datetime.timedelta(0, 64800)

Please ignore what is below (it does return different value) -

Added: Irrespective of the time value in the table, python-MySQLdb seems to only return datetime.timedelta(0, 64800).

Note: I use Python 2.4

Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
Sam
  • 681
  • 1
  • 7
  • 12

2 Answers2

48

It's strange that Python returns the value as a datetime.timedelta. It probably should return a datetime.time. Anyway, it looks like it's returning the elapsed time since midnight (assuming the column in the table is 6:00 PM). In order to convert to a datetime.time, you can do the following::

value = datetime.timedelta(0, 64800)
(datetime.datetime.min + value).time()

datetime.datetime.min and datetime.time() are, of course, documented as part of the datetime module if you want more information.

A datetime.timedelta is, by the way, a representation of the difference between two datetime.datetime values. So if you subtract one datetime.datetime from another, you will get a datetime.timedelta. And if you add a datetime.datetime with a datetime.timedelta, you'll get a datetime.datetime. That's how the code above works.

Rick Copeland
  • 11,672
  • 5
  • 39
  • 38
  • 3
    I should mention that my first attempt was to use `datetime.time.min + value`, but this does not work, as `datetime.time` objects cannot be added to `datetime.timedelta` objects. – Rick Copeland Apr 18 '09 at 21:05
  • Python returns only datetime.timedelta(0, 64800), irrespective of the actual time value. – Sam Apr 18 '09 at 21:14
  • 1
    Yeah, I got fed up of the different ways dates and times are signalled across different DB-API layers, different versions of MySQLdb and Python, and even different available libraries (ie. mxDateTime). Now I just use integers and Unix time. Unless you need to do queries based on day-of-week or something, this is generally much easier. – bobince Apr 19 '09 at 02:06
  • 2
    minor caveat: if 'value' is negative this won't work, because you can't add smaller than datetime.min – Richard Jun 18 '13 at 22:30
2

It seems to me that the TIME type in MySQL is intended to represent time intervals as datetime.timedelta does in Python. From the docs you referenced:

TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

An alternative to converting from datetime.timedelta to datetime.time would be to change the column type to DATETIME and not using the date fields.

-Insert:

tIn = datetime.datetime(
    year=datetime.MINYEAR, 
    month=1, 
    day=1, 
    hour=10,
    minute=52,
    second=10
    )
cursor.execute('INSERT INTO TableName (TimeColumn) VALUES (%s)', [tIn])

-Select:

 cursor.execute('SELECT TimeColumn FROM TableName')
 result = cursor.fetchone()
 if result is not None:
     tOut = result[0].time()
     print 'Selected time: {0}:{1}:{2}'.format(tOut.hour, tOut.minute, tOut.second)

datetime.time() is called on a datetime object to get a time object.

nnguyen
  • 128
  • 5