25

I have a database of test records with one column 'test_time' defined as datetime. I want to query how many distinct dates there are as I want to dump test results to csv according to dates. I now have the following:

distinct_dates = list(session.query(Test_Table.test_time).distinct())

But this gives me a list of datetime not date. Certainly I can convert it in Python but when I am using sqlite. I did this SELECT DISTINCT DATE(test_time) FROM Test_Table. I cannot come up with the equivalent in sqlalchemy.

foresightyj
  • 2,006
  • 2
  • 26
  • 40

1 Answers1

51

That would be by using the cast() expression:

from sqlalchemy import cast, Date

distinct_dates = session.query(cast(Test_Table.test_time, Date)).distinct().all()

or the shortcut method:

distinct_dates = session.query(Test_Table.test_time.cast(Date)).distinct().all()

and if using SQLite, give this a shot:

distinct_dates = session.query(func.DATE(Test_Table.test_time)).distinct().all()
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Matthew Graves
  • 3,226
  • 1
  • 17
  • 20
  • Without looking at the sourcecode I would imagine this ends up using the DATE() expression in sql... there is a possibility it doesn't do that, but I have faith in sqlalchemy. – Matthew Graves Jun 27 '13 at 03:42
  • I tried `cast` before. But sqlalchemy always throws me this exception: `ValueError: Couldn't parse date string '2013' - value is not a string.` – foresightyj Jun 27 '13 at 07:34
  • I seemed to have figured out why. I am using sqlite. I turned on echo of sqlalchemy and the actual query is `SELECT DISTINCT CAST(test_table.test_time AS DATE) AS anon_1 FROM test_table`. And it seems that sqlite does not support DATE. Instead of returning whole date, it returns only year. Thanks! – foresightyj Jun 27 '13 at 07:45
  • 2
    However, if I run the raw query `SELECT DISTINCT DATE(test_table.test_time) AS test_date FROM test_table`, it will give me whole dates. This [link](http://sqlite.1065341.n5.nabble.com/CAST-td23755.html) talked about the weirdness about casting sqlite datetime to date. – foresightyj Jun 27 '13 at 07:48
  • You are super! That did work! I did see the actual query like `SELECT DISTINCT DATE(test_table.test_time) AS "DATE_1" FROM test_table`. Thanks a million! – foresightyj Jun 27 '13 at 09:03
  • 1
    no problem, glad I could help! sqlalchemy is very well thought out, if only their documentation was a little better! – Matthew Graves Jun 27 '13 at 09:04
  • 1
    True indeed. I am new to SQL and even newer to sqlalchemy. But I can generally find my ways out in SQL. sqlalchemy looks must more complex. – foresightyj Jun 27 '13 at 09:24