Aggregate functions over datetime do not convert object for SQLite

[Former user]

func.max and func.min do not convert result object to correct datetime object.

---testcase.py

import sqlalchemy as sqa
import datetime


db = sqa.create_engine("sqlite://")
metadata = sqa.BoundMetaData(db)

#metadata.engine.echo = True # debug output

events_table = sqa.Table('events', metadata,
  sqa.Column('id', sqa.Integer, primary_key=True),
  sqa.Column('name', sqa.String(255)),
  sqa.Column('datetime', sqa.DateTime),
)
events_table.create()

i = events_table.insert()
name = "just-a-name"
i.execute(name = name, datetime = datetime.datetime(2007, 7, 26, 10, 23, 44))
i.execute(name = name, datetime = datetime.datetime(2007, 7, 26, 12, 43, 44))
i.execute(name = name, datetime = datetime.datetime(2007, 7, 26, 17, 23, 34))
i.execute(name = name, datetime = datetime.datetime(2007, 7, 26, 22, 54, 23))
i.execute(name = name, datetime = datetime.datetime(2007, 7, 26, 23, 00, 18))


date = "2007-07-26"
test1 = sqa.select(
           [events_table.c.datetime](events_table.c.datetime),
           (sqa.func.date(events_table.c.datetime) == date) &
           (events_table.c.name == name)
         ).execute().fetchone()
print "--"
print test1
print type(test1)
print type(test1[0](0))
print "-"
test2 = sqa.select(
           [sqa.func.max(events_table.c.datetime)](sqa.func.max(events_table.c.datetime)),
           (sqa.func.date(events_table.c.datetime) == date) &
           (events_table.c.name == name)
         ).execute().fetchone()
print test2
print type(test2)
print type(test2[0](0))

---

---output

(datetime.datetime(2007, 7, 26, 10, 23, 44),)
<class 'sqlalchemy.engine.base.RowProxy'>
<type 'datetime.datetime'>
-
(u'2007-07-26 23:00:18.0',)
<class 'sqlalchemy.engine.base.RowProxy'>
<type 'unicode'>

[Mike Bayer]

Sorry, this one has a lot of caveats, since SQLite doesn't know anything about dates. SQLAlchemy needs to know exactly every SQL function which is supposed to return a date, it needs to receive date-based arguments as dates, not strings, and the column needs to be named in order for the result set to target it for result set processing. I dont see any way to work around any of this nor for SA to "guess" that this is what you wanted. possibly that a. if a function is in a columns clause without a name, it raises a warning and b. if we implement #615, we put some knowledge of max and min that their return type is the same as their argument. for here, the correct statement is:

test2 = sqa.select(
           [type=sqa.DateTime).label('max')](sqa.func.max(events_table.c.datetime,),
           (sqa.func.date(events_table.c.datetime, type=sqa.Date) == datetime.date(2007, 7, 26)) &
           (events_table.c.name == name)
         )

ill leave this one open in case someone wants to make some suggestions.

[Mike Bayer]

this will be handled by #615.