Android Python MySQL Datetime Parsing

Question

I need to pass datetimes between an android client, a python server and a mysql server.

It should work the following:

• the android client sends the exact time from the client to the cherrypy python server (I guess the datetime object should be sent as a string?)
• the server has to parse this string into something useful to work with as a datetime object

Now there a two cases:

1. the datetime object should be written into a mysql database (there is an attribute of type DATETIME in the related database table)
2. the server should retrieve a datetime from the mysql database and compare it with the parsed datetime object

After some background processes finished their work with the python datetime objects as input parameters, a new datetime object should be passed back to the android client

Does anyone know some good solution for solving these problems?

Answer 1

The best format for transferring datetime values is the ISO 8601 standard; they come in the format YYYY-mm-ddTHH:MM:SS+tz:tz, where the T is optional.

Python's datetime.datetime class can parse these with a simple extra module (see How to parse an ISO 8601-formatted date?). Output is just as easy with the .isoformat() method.

MySQL's DATETIME column type only deals with UTC values, but by default accepts ISO 8601 datetime strings (with a space instead of a T), so you'd have to cast your datetime objects to UTC (example with iso8601 module mentioned above):

import iso8601
utciso8601 = dt.astimezone(iso8601.iso8601.UTC).isoformat(' ')[:19]

I'd insert the timezone offset into the database too; simply use the tzname() method to retrieve it from the datetime object, then parse it out again when loading from MySQL with the iso8601.iso8601.parse_timezone() function.

# insertion:
cursor.execute('INSERT INTO dates VALUES(?, ?)', utciso8601, dt.tzname())

# querying
for row in query:
    timezone = iso8601.iso8601.parse_timezone(row[1])
    utcdt = iso8601.parse_date(row[0])
    dt = utcdt.astimezone(timezone)

I don't know how well Android deals with dates and times, but surely it can handle ISO 8601 formats just fine.