TypeError: can't compare offset-naive and offset-aware datetimes

Question

The server that hosts my db is in the U.S. When I add an item to the db, I want the time to be stored using Australia/Sydney time. Regardless of what country the user is in, if they retrieve this record, it should be returned using Australia/Sydney time. Currently I am doing this:

tz = pytz.timezone('Australia/Sydney')
orderDate = datetime.now(tz)
return add_order_to_db(orderID, orderDate, orderCost) # Adds an ORDER object to db

The issue arises when I want to update the orderDate for the above ORDER object.

tz = pytz.timezone('Australia/Sydney')
orderDate = datetime.now(tz)

with contextlib.closing(DBSession()) as session:
    try:
        existingOrder = session.query(ORDER).filter_by(ORDERSID=orderID).first()
        existingOrder.ORDERSDATE = orderDate
        session.commit()
    except exc.SQLAlchemyError, error:
        session.rollback()
        raise_database_error(error)

ORDER object

class ORDER(Base):
__tablename__ = 'ORDERS'

ORDERSID = Column(Integer, primary_key=True)
ORDERSDATE = Column(DateTime, nullable=False)
ORDERSCOST = Column(Numeric(19, 4), nullable=False)

In mySQL, ORDERSDATE is a Datetime variable.

it looks like MySQL-python doesn't support timezone-aware datetime objects. You could try to set Australia/Sydney timezone for the MySQL db connection and use naive datetime objects (if TIMESTAMP type is used) or just strip timezone info before putting it into the db and reattaching it while retrieving from the db). Or (better) use naive datetime object to represent UTC time (otherwise some time values can be ambiguous) — jfs

AdamG AdamG · Accepted Answer · 2015-04-10T03:25:21

How about retaining the offset-naive nature of the datetime object by just applying the offset to the current UTC time?

 tz = pytz.timezone('Australia/Sydney')
 orderDate = datetime.datetime.now() +  tz._utcoffset

TypeError: can't compare offset-naive and offset-aware datetimes

1 Answers