Take the 2-minute tour ×

I have a SQLAlchemy declarative class with a datetime field in it, like:

class Logon(Base):
    id = Column(Integer, primary_key=True)
    timestamp = Column(types.DateTime)
    event_type = Column(types.Integer)

I am interested in getting all dates for which there is at least an event, hence I use a distinct clause to play with:


Obviously that is boggus because timestamps are not unique within the same day. However it is strange that I get unicode strings instead of datetime instances:

 (u'2012-07-26 11:05:01.000000',), (u'2012-07-26 11:12:53.000000',), (u'2012-07-27 16:53:28.000000',)

What I really want is the distinct operator to use date and not datetime:


The SQL looks also OK to me:

SELECT distinct(CAST(logons.timestamp AS DATE)) AS distinct_1 
FROM logons

However to my surprise the result of my query is a single entry with the year:


I could get my list of dates with an easy loop, but I am interested in learning how to combine the cast and distinct operators within SQL and/or SQLAlchemy

share|improve this question
This doesn't look right. What database are you using? Might be good to post the engine URL. –  sayap Sep 3 '12 at 22:52
I am using the SQLite engine –  SystematicFrank Sep 4 '12 at 7:55
@sayap which part doesn't look right to you? the first distinct without cast that retrieves tuples with unicode strings instead datetime or the second one with cast? –  SystematicFrank Sep 4 '12 at 7:57
Oh, I mean the part where a string is returned instead of a datetime or a date. Both the code and the SQL look fine, and will definitely work with postgresql and mssql. Probably some quirk with sqlite, which I don't have much experience with. –  sayap Sep 4 '12 at 8:13

Your Answer



Browse other questions tagged or ask your own question.