MySQL doesn't accept timezone offsets in datetime literals

[Will Ross]

Datetime literals with a timezone offset ("+0000" or "-0500") are invalid in MySQL. The issue is more visible with MySQL 5.6 because STRICT_TRANS_TABLES was added to the default configuration, causing statements that worked in older versions to fail. If no tzinfo is given, the datetime works fine. I'd have submitted a patch for this, but I'm not sure where to look in the code. That and I'm not sure it's even an actual bug, or intended behavior for backends that don't support timezones being passed timezone aware objects.

[Mike Bayer]

im voting +1 to close this, SQLAlchemy doesn't parse python datetime objects, they are handed straight to your backend on all platforms except SQLite, in this case MySQL-python. I suggest you submit the bug report with them. On the SQLA side, you can augment custom types with whatever pre-processing logic you like as per http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#augmenting-existing-types.

[Mike Bayer]

yeah sorry, this is kind of out of our wheelhouse. MySQL doesn't have a timezone-aware type in any case: http://dev.mysql.com/doc/refman/5.5/en/datetime.html

note our timezone docs:

DateTime

If True, and supported by the backend, will produce ‘TIMESTAMP WITH TIMEZONE’. For backends that don’t support timezone aware timestamps, has no effect.

below is a script illustrating the plain MySQLdb equivalent, the call with the timezone fails:

import MySQLdb
import datetime as dt

conn = MySQLdb.connect(
    user="scott", passwd="tiger",
    host="localhost", db="test")

cursor = conn.cursor()

try:
    cursor.execute("drop table timestamps")
except:
    pass


class UTC(dt.tzinfo):

    def utcoffset(self, obj):
        return dt.timedelta()

    def dst(self, obj):
        return dt.timedelta()

    def tzname(self, obj):
        return u'UTC+00:00'

utc = UTC()
cursor.execute("""
    create table timestamps (
        id integer primary key AUTO_INCREMENT,
        data DATETIME
    )
""")


# works
cursor.execute("insert into timestamps (data) values (%s)", dt.datetime.utcnow())


# fails
cursor.execute("insert into timestamps (data) values (%s)", dt.datetime.now(utc))