Filter by year in datetime column

[martin.s...@gmail.com]

Hello, pleas, i have begginer problem and question:

In table (database is sqlite) is colum for create date (create_date =
Field(DateTime, default = datetime.now))

I need query from table, with all item where have year of create date
2007.

Is this the right way ? (this don`t work)
data = Table.query().filter(func.year(Mikropost.c.create_date) ==
2008)

[Rick Morrison]

There are no generic date functions in SQLAlchemy (although work has begun on them). So for now, you'll need to use date functions native to your database.

For sqlite something like,

    func.strftime('%Y', Mikropost.c.create_date) == '2008'

should work -- you may need to add additional percent signs depending on how badly the '%Y' literal is mangled during parameter collection.

[King Simon-NFHD78]

Would "create_date >= '2007-01-01' and create_date < '2008-01-01'" be
acceptable?

If so, something like this should work

from sqlalchemy import and_
from datetime import date

data = Table.query().filter(and_([Mikropost.c.create_date >= date(2007,
1, 1),
Mikropost.c.create_date < date(2008,
1, 1)]))

Hope that helps,

Simon

[Andreas Jung]

--On 18. Januar 2008 12:08:46 -0500 Rick Morrison <rickmo...@gmail.com>
wrote:

Such operations will likely trigger a full table scan. Depending on the
size of your data you might adjust the data model to the need of your query
and/or think about functional indexes (in case your database provides this
functionality).

Andreas

[Rick Morrison]

> Such operations will likely trigger a full table scan

SQLite dates are stored as strings anyway, AFAIK there is little one can do to avoid table-scans in SQLite based solely on date criteria. I use julian dates stored as integers when working with large datasets in SQLite, and convert as needed. Be interested to hear what others do about this.

[sdo...@sistechnology.com]

well one can decompose dates into day-month-year triple of ints, but this
definitely will need some wrapper to be made easy-to-use (and may need
composite keys for example).